ADD New Question

[OldEnough]

Just learning to use ADO. The code example below illustrates what I'd like to
do using ADO, though I'm not sure quite how to do it.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMyTable", dbOpenDynaset)
rs.AddNew
rs!MyField1= Me.Field1
rs!MyField2 = Me.Field2
rs.Update
rs.Close
Set rs = Nothing

As close as I can come the ADO would look something like this (Help Please)

Dim AccessConnect as String
AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};†& _
“Dbq=" & BackEndPath & "; DefaultDir=" & FrontEndPath & ";" & _
"Uid=Admin;Pwd=;"

Dim oRS As New ADODB.Recordset
Dim cnn As New ADODB.Connection
cnn.ConnectionString = AccessConnect
cnn.Open

Set oRS = New Recordset (Not sure if this is correct)
With oRS
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.AddNew
oRS!MyField1= Me.Field1
oRS!MyField2 = Me.Field2
.Update
.Close
End With

cnn.Close
cnn.ConnectionString = ""

Corrections to this non-working code would be appreciated.

 

[Brendan Reynolds]

Just learning to use ADO. The code example below illustrates what I'd like
to
do using ADO, though I'm not sure quite how to do it.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMyTable", dbOpenDynaset)
rs.AddNew
rs!MyField1= Me.Field1
rs!MyField2 = Me.Field2
rs.Update
rs.Close
Set rs = Nothing

As close as I can come the ADO would look something like this (Help
Please)

Dim AccessConnect as String
AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};†& _
“Dbq=" & BackEndPath & "; DefaultDir=" & FrontEndPath & ";" & _
"Uid=Admin;Pwd=;"

Dim oRS As New ADODB.Recordset
Dim cnn As New ADODB.Connection
cnn.ConnectionString = AccessConnect
cnn.Open

Set oRS = New Recordset (Not sure if this is correct)
With oRS
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.AddNew
oRS!MyField1= Me.Field1
oRS!MyField2 = Me.Field2
.Update
.Close
End With

cnn.Close
cnn.ConnectionString = ""

Corrections to this non-working code would be appreciated.

Well one thing that stands out at first glance, is that you never assign
your connection object to the ActiveConnection property of the recordset ...

With oRs
Set .ActiveConnection = cnn

BTW, it looks like you're trying to use ADO and the ODBC driver for Access
to update data in a JET database. That would be somewhat akin to sailing
from Europe to America by way of Australia. I really can't think of any
reason to do that. (To use ADO and ODBC to update a JET database, that is.
Sailing from Europe to America might be the right thing to do, if you're
sailing for the fun of it.)

 

[OldEnough]

Thanks for looking at this. I added the line you suggested but it still
throws an error. "Error 3074 Operation not allowed when object is closed." To
answer your other point. I would like to migrate the current application to
SQL Server, but I don't know enough to be able to do it. This is a step along
the way, one of the thousands of things I don't yet understand.

 

[Douglas J. Steele]

Try removing the New keyword from the declarations: it's problematic at
best. As well, you never open the recordset. Try

Dim AccessConnect as String
Dim oRS As ADODB.Recordset
Dim cnn As ADODB.Connection

AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & BackEndPath & "; DefaultDir=" & FrontEndPath & ";" & _
"Uid=Admin;Pwd=;"

Set cnn = New ADODB.Connection
cnn.ConnectionString = AccessConnect
cnn.Open

Set oRS = New Recordset
With oRS
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
.AddNew
!MyField1= Me.Field1
!MyField2 = Me.Field2
.Update
.Close
End With

cnn.Close


I'm assuming that BackEndPath and FrontEndPath are defined somewhere...

 

[OldEnough]

Thanks for your help! I had one small tweak to make to get it working, but
your example helped. I had to add "MytblName" to the .Open line before it
worked.

i.e .Open ("MytblName")