Use ADO in VBA

[d_profesor]

Can we use ADO in VBA for access 2007? Cozz when i declare variable for connection such as: dim curconn as New ADODB.Connection, run it
i got message error: " User define type not defined"

I need to use ADO object to manipulate my Access aplication

help me pls..

sory i have solved it, , but i need to know about ADO version, there are many version in references, ADO 2.0.-ADO 2.7. which one the best? the last (2.7) ?

Thanks

 

[Moniker]

ADO goes higher than 2.7. However, the more important question is, what is the highest version of ADO on the oldest machine in there? For example, do you have an Access 2000 user with ADO 2.5 as the highest? You want to aim for the lowest common denominator here so that you don't get reference errors when switching between computers. You'll see that a lot in here where someone develops on Access 2003 or 2007, and then they are confused when it's not fully functional in Access 2000. 99% of the time, it's just a reference that broke, but it's not always the easiest thing for an end-user to fix.

 

[tehNellie]

If you're in an enviroment where people might be using different versions of Access/Office then Late binding (using ADO, in this instance, without explicitly setting a reference) might be worth investigating as an alternative to Moniker's suggestion of going with the lowest known version.

There are plenty of threads in here that cover Early and Late binding and I'm sure you'll find people happy to slate/champion each method, but in this case I would suggest that it is something worth investigating.

 

[d_profesor]

Thanks, but i have the next question..

I tried this code in ACCESS 2007:

Dim curconn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim curDb As Database
Set curDb = CurrentDb
Set curconn = New ADODB.Connection
With curconn
.Provider = "Microsoft.Jet.oledb.4.0"
.ConnectionString = "data source=" & curDb.Name
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "SELECT*FROM [Asset Movement] WHERE [Asset ID]='" & _
Me![Asset ID] & "'", curconn, , , adCmdText

But.. when i run it, the message said: " unrecognized database format....

so how to set connection, and open recordset in access 2007??

help please..

 

[boblarson]

I tried this code in ACCESS 2007:

Dim curconn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim curDb As Database
Set curDb = CurrentDb
Set curconn = New ADODB.Connection
With curconn
.Provider = "Microsoft.Jet.oledb.4.0"
.ConnectionString = "data source=" & curDb.Name
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "SELECT*FROM [Asset Movement] WHERE [Asset ID]='" & _
Me![Asset ID] & "'", curconn, , , adCmdText

But.. when i run it, the message said: " unrecognized database format....

so how to set connection, and open recordset in access 2007??

help please..

If you are connecting to the current database, you don't need all of that. Just use

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst.Open "SELECT*FROM [Asset Movement] WHERE [Asset ID]='" & Me![Asset ID] & "'", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

 

[d_profesor]

ohh so for current database, connection implicitly already exist?

 

[boblarson]

ohh so for current database, connection implicitly already exist?

Essentially, yes.