Converting from Access db to SQL Server

[Hardhat4u]

Merry Christmas,

I am try to convert my VB code which use to connect to an MS Access database (V 2.0) and now is an SQL Server db. I know little about it but I've been reading. I seem to have connected properly to the SQL Database and I'm trying to simply confirm I can see the records of a query I wrote that connects to one of the table in the sql db. Here's what I have....

Dim mydatabase As Database
Dim myquerydef As QueryDef
Dim SPTQueryName As String
Dim SQLString As String
Dim ConnectString As String

Set mydatabase = DBEngine.Workspaces(0).Databases(0)
Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)

ConnectString = "ODBC;DSN=TCMSQL;Description=TCMSQL;UID=TCMUSER;PW D=lyssaeric;DATABASE=TCM;Network=DBMSSOCN"
SQLString = "SELECT JCJob.JobStructure1, JCJob.Name FROM JCJob ORDER BY JCJob.JobStructure1 aSC;"

myquerydef.Connect = ConnectString
myquerydef.SQL = SQLString

"I want to verify I can return records by simply having the first record come up in a message box. It does not see the SQLString when I type:

MsgBox myquerydef![JobStructure1]


myquerydef.Close

It does not see the pass-through query I know but why? Seem like I have to define some recordset.



Steve

[pbaldy]

Well for starters, you can't get the value that way. Simplest might be a DLookup():

MsgBox DLookup("JobStructure1", "myquerydef")

I'd put that after closing the QueryDef to make sure it's saved. If the table is linked, you don't need a pass through query. With a pass through, I wouldn't create a new one, I'd modify the SQL of an existing one.

[pbaldy]

By the way, you never set SPTQueryName, and it's the name you'd want to use in the DLookup.

[Hardhat4u]

Thank you...

so if I simply give it a name, that will do it?

[pbaldy]

Well, no. Like I said, you can't refer to the query the way you did in the message box.

Just noticed you're in Sac. I was raised there, and my mom still lives there. I'm down there fairly often.

[Hardhat4u]

Okay got it. I've only been here 19 months. Lived all but 2 out of my soon-to-be 62 years outside of Boston. I like it here but miss the kids and grandkids. I just got back from the east coast for an early holiday.

[Hardhat4u]

pbaldy,

I finally did get what I want but trying a different approach now by attaching an SQL table called APVendor and trying to filter a record from it like I did in access thousands of times. Here's the problem. When I write a query in VB using the attached sql table, it blows right by the criteria if I set the criteria as a variable. Here's my code...

Dim db As Database
Dim ssJob As Recordset
Dim sqlJob, VId As String

Set db = CurrentDb()

VId = CStr(Me![Text2])
sqlJob = "SELECT dbo_APVendor.Vendor, dbo_APVendor.Name FROM dbo_APVendor WHERE (((dbo_APVendor.Vendor) = "" & VId & "") And ((dbo_APVendor.CompanyId) = ""1"")) ORDER BY dbo_APVendor.Vendor, dbo_APVendor.Name;"
Set ssJob = db.OpenRecordset(sqlJob, dbOpenSnapshot)


MsgBox ssJob![Name]

"The error message that gets returned is Too few parameters, expected 1."

If I use that same code using an access db, it works perfectly.

Obviously, writing a query in VBA using a SQL table reacts differently.

Any help?

[pbaldy]

Try:

sqlJob = "SELECT dbo_APVendor.Vendor, dbo_APVendor.Name FROM dbo_APVendor WHERE dbo_APVendor.Vendor = '" & VId & "' And dbo_APVendor.CompanyId = '1' ORDER BY dbo_APVendor.Vendor, dbo_APVendor.Name;"

If either field in the criteria has a numeric data type, remove the single quotes around it.

[Hardhat4u]

Paul, that did it. I really appreciate the help you've given me. This opens up many more doors.

Hope you had a great holiday.

S

[pbaldy]

Happy to help Steve!

Had a quiet holiday; Thanksgiving was the hectic one with 20+ family in town. Hope you had a great one too!