Re: ADD New Question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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...


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"OldEnough" <OldEnough@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:23724A5E-1A81-496C-AAFA-B2A1FF2DB8F7@xxxxxxxxxxxxxxxx
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.


.

Relevant Pages

  • Re: ADO Recordset.Filter in Excel 2002
    ... Tim ... Dim RS As ADODB.Recordset ... Dim oRS As ADODB.Recordset ... 'Populate the Recordset object with a SQL query ...
    (microsoft.public.excel.programming)
  • Re: query after insert fails
    ... MyTableUpdate sub that does the oRS insert: ... such as you do in the first sub in your posted code. ... Dim db As DAO.Database ... Flag = Flag + 1 ...
    (microsoft.public.access.modulesdaovba)
  • Re: query after insert fails
    ... If I add the line oRS.Close just before Setting oRS to Nothing it throws a ... Values are passed to it by the calling sub. ... Dim db As DAO.Database ... Flag = Flag + 1 ...
    (microsoft.public.access.modulesdaovba)
  • Re: ADO Recordset.Filter in Excel 2002
    ... Set oRS = Nothing ... Tim Williams ... > Dim RS As ADODB.Recordset ... > 'Populate the Recordset object with a SQL query ...
    (microsoft.public.excel.programming)
  • Re: ADD New Question
    ... Dim AccessConnect as String ... Dim oRS As ADODB.Recordset ... Dim cnn As New ADODB.Connection ...
    (microsoft.public.access.externaldata)