migrating code from Access 2000 to 2003 and 2007

[Marc]

This works perfectly ion Access 2000, no so in access 2003 & 2007: can
somebody help me with that?

Dim cn As ADODB.Connection
Dim db As Database
Dim rs As Recordset

On Error GoTo fout

Set db = CurrentDb
Set cn = CurrentProject.Connection
'Set rs = db.OpenRecordset("Raadplegingen")
Set rs = New ADODB.Recordset
rs.Open "Raadplegingen", cn, adOpenDynamic, adLockOptimistic

DBEngine.BeginTrans
rs.AddNew
rs!raadplegingsdatum = Date
rs!raadplegingstijd = Time
rs!Gebruiker_ID = Gebruikers_ID
rs!Computer_naam = Computer
Computer = Right(Computer, 8)
rs!login_datum = login_datum
rs!login_tijd = login_tijd
rs!Patiënt_ID = Me.Dossiernummer
rs.Update
DBEngine.CommitTrans

hernemen:
Exit Function
fout:
Select Case Err.Number
Case Else
MsgBox Err.Number & ": " & Err.Description, vbOKOnly,
"Dossier_CMD_Click"
Resume hernemen
End Select

Many thanks,


Marc

 

[Paul Shapiro]

This works perfectly ion Access 2000, no so in access 2003 & 2007: can
somebody help me with that?

Dim cn As ADODB.Connection
Dim db As Database
Dim rs As Recordset

On Error GoTo fout

Set db = CurrentDb
Set cn = CurrentProject.Connection
'Set rs = db.OpenRecordset("Raadplegingen")
Set rs = New ADODB.Recordset
rs.Open "Raadplegingen", cn, adOpenDynamic, adLockOptimistic

DBEngine.BeginTrans
rs.AddNew
rs!raadplegingsdatum = Date
rs!raadplegingstijd = Time
rs!Gebruiker_ID = Gebruikers_ID
rs!Computer_naam = Computer
Computer = Right(Computer, 8)
rs!login_datum = login_datum
rs!login_tijd = login_tijd
rs!Patiënt_ID = Me.Dossiernummer
rs.Update
DBEngine.CommitTrans

hernemen:
Exit Function
fout:
Select Case Err.Number
Case Else
MsgBox Err.Number & ": " & Err.Description, vbOKOnly,
"Dossier_CMD_Click"
Resume hernemen
End Select

First, it would be easier for someone to offer advice if you give more
information than "it doesn't work". What's the error message? Use the
debugger to see what line of code is generating the error.

You could try disambiguating the dim statements. Both DAO and ADODB have a
Recordset object. If you don't specify which one you are declaring, it will
be whichever is listed earlier in the VBA References.
Dim db As DAO.Database 'This doesn't seem to be used?
Dim rs As ADODB.Recordset

 

[Marc]

First, it would be easier for someone to offer advice if you give more
information than "it doesn't work". What's the error message? Use the
debugger to see what line of code is generating the error.

You could try disambiguating the dim statements. Both DAO and ADODB have a
Recordset object. If you don't specify which one you are declaring, it will
be whichever is listed earlier in the VBA References.
Dim db As DAO.Database    'This doesn't seem to be used?
Dim rs As ADODB.Recordset

Paul,

Thanks for your reaction,

in fact it's while debugging that the step 'rs.Open "Raadplegingen",
cn, adOpenDynamic, adLockOptimistic' gives already a problem; i'm
sorry to admit that i'am not so familiar that kind of connection
problem. first you have to know that that front end is Access, the
back end being a SQL server table.

As a matter the DAO 3.6 Object library has to be active in the
reference list in the program code. i found that piece of equipment
via a friend, but he is not available for the moment and i'am kind of
stuck now.

Please note that i'am leaving for Paris until Monday; i attend a
convention down there.

Kind regards,

Marc

 

[Paul Shapiro]

Paul,

Thanks for your reaction,

in fact it's while debugging that the step 'rs.Open "Raadplegingen",
cn, adOpenDynamic, adLockOptimistic' gives already a problem; i'm
sorry to admit that i'am not so familiar that kind of connection
problem. first you have to know that that front end is Access, the
back end being a SQL server table.

As a matter the DAO 3.6 Object library has to be active in the
reference list in the program code. i found that piece of equipment
via a friend, but he is not available for the moment and i'am kind of
stuck now.

Please note that i'am leaving for Paris until Monday; i attend a
convention down there.

Kind regards,
Marc

Did you update the dim statement to 'Dim rs As ADODB.Recordset'? What is the
error message? Is your frontend database an adp or an mdb? If it's an adp,
then that code looks ok, except perhaps for the sql statement. If the
command is being passed to sql server, then you may need to use:
Select * From Raadplegingen
instead of just
Raadplegingen.

If you're just opening the recordset to add a new record, you could use:
Select * From Raadplegingen Where 1=0
to avoid returning unused rows.

Or maybe use an Insert sql statement and an ado command object.

 

[Eighte]

Gossip speaders..sexy and cute buns.=Rar too is with it! wardrobe to
equalise. hmm.. nicely made.hmm..sedap. :].

 

[Eighte]

Bless that some understands ME.

Did you update the dim statement to 'Dim rs As ADODB.Recordset'? What is
the error message? Is your frontend database an adp or an mdb? If it's an
adp, then that code looks ok, except perhaps for the sql statement. If the
command is being passed to sql server, then you may need to use:
Select * From Raadplegingen
instead of just
Raadplegingen.

If you're just opening the recordset to add a new record, you could use:
Select * From Raadplegingen Where 1=0
to avoid returning unused rows.

Or maybe use an Insert sql statement and an ado command object.