access to Access

[billritz]

Trying to connect to an Access database, I copied some production code that works with the same .accdb file- why type it again, right?
'open connection
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TestData.accdb;"


'build recordset
sSQL = "SELECT * FROM tblTest;"
Set rs = CreateObject("ADODB.Recordset")
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic

It blows up, highlighting the word adOpenDynamic saying variable not defined. So I open the other project, hit the compile under debug, and it goes right through! I go back to the new one, and try to run it, and boom. If the old code runs, the correct libraries are open, so what could cause this?

Next, I changed the parameters to 2 and 3 (below) and it compiles, but the first time I do a .Find on the rs it gives me a 3001 (bad arguments) so it's really not opening correctly, though the same code is working on the same .accdb file in another workbook open in the same session. Weird. Any help would be appreciated.

rs.Open sSQL, cn, 2, 3 'adOpenDynamic, adLockOptimistic

 

[jamcall]

In your Visual Basic window, go to Tools | References and ensure that Microsoft ActiveX Data Objects are selected - check the other project to see which one(s) it uses...

HTH,
~ Jim

 

[Norie]

What happens if you set the same references in the new project as those in the original project?