Upsize to SQL ODBC error on rs!update

D

deadtrees

I upsized to SQL, linked all the tables and am testing each field. My db is
comprised of an Access front end and two SQL BE db's (one for data, one for
audit trail)

The audit trail is populated by some vba. After the upsize, I added the
dbOpenDynaSet and dbSeeChanges to get the audit code working. Now, it works
on the mainform, but any subform audits come back with a 3146 ODBC runtime
error and the debugger stops on the rs!update line of code.

I'm still pretty new to the whole ODBC world and my vba is pretty rudimentary
as well. Do I need to create a new workspace now that I'm using SQL as a
backend or can I just update my rs statement to include different types and
options to something like

Set rs = db.OpenRecordset("tbl__ChangeTracker", dbOpenDynaset,
dbSeeChanges+dbRunAsync, dbOptimisticValue)


Here's the code.

Sub TrackChanges(F As Form)

Dim ctl As Control, frm As Form

Dim MyField As String, MyKey As Long, MyTable As String

Dim db As DAO.Database, rs As DAO.Recordset

'On Error Resume Next

Set frm = F

Set db = CurrentDb

Set rs = db.OpenRecordset("tbl__ChangeTracker", dbOpenDynaset,
dbSeeChanges)

With frm

MyTable = .Tag

' find the primary key & its value, based on the Tag

For Each ctl In .Controls

If ctl.Tag = "PK" Then

MyField = ctl.Name

MyKey = ctl

Exit For

End If

Next ctl

For Each ctl In .Controls

' inspect only data-bound controls

Select Case ctl.ControlType

Case acTextBox, acComboBox, acCheckBox

If Nz(ctl.ControlSource, "") > "" Then

' if changed, record both old & new values

If Nz(ctl.OldValue, "") <> Nz(ctl, "") Then

rs.AddNew

rs!FormName = .Name

rs!MyTable = MyTable

rs!MyField = MyField

rs!MyKey = MyKey

rs!ChangedOn = Now()

rs!FieldName = ctl.Name

If ctl.ControlType = acCheckBox Then

rs!Field_OldValue = YesOrNo(ctl.OldValue)

rs!Field_NewValue = YesOrNo(ctl)

Else

rs!Field_OldValue = Left(Nz(ctl.OldValue, ""),
255)

rs!Field_NewValue = Left(Nz(ctl, ""), 255)

End If

rs!UserChanged = UserName()

rs!CompChanged = CompName()

rs!Update <-- THIS IS WHERE THE DEBUGGER
TAKES ME

End If

End If

End Select

Next ctl

End With

rs.Close

Set rs = Nothing

Set db = Nothing

End Sub



Private Function YesOrNo(v) As String

Select Case v

Case -1

YesOrNo = "Yes"

Case 0

YesOrNo = "No"

End Select

End Function
 
O

orange via AccessMonster.com

deadtrees said:
I upsized to SQL, linked all the tables and am testing each field. My db is
comprised of an Access front end and two SQL BE db's (one for data, one for
audit trail)

The audit trail is populated by some vba. After the upsize, I added the
dbOpenDynaSet and dbSeeChanges to get the audit code working. Now, it works
on the mainform, but any subform audits come back with a 3146 ODBC runtime
error and the debugger stops on the rs!update line of code.

I'm still pretty new to the whole ODBC world and my vba is pretty rudimentary
as well. Do I need to create a new workspace now that I'm using SQL as a
backend or can I just update my rs statement to include different types and
options to something like

Set rs = db.OpenRecordset("tbl__ChangeTracker", dbOpenDynaset,
dbSeeChanges+dbRunAsync, dbOptimisticValue)

Here's the code.

Sub TrackChanges(F As Form)

Dim ctl As Control, frm As Form

Dim MyField As String, MyKey As Long, MyTable As String

Dim db As DAO.Database, rs As DAO.Recordset

'On Error Resume Next

Set frm = F

Set db = CurrentDb

Set rs = db.OpenRecordset("tbl__ChangeTracker", dbOpenDynaset,
dbSeeChanges)

With frm

MyTable = .Tag

' find the primary key & its value, based on the Tag

For Each ctl In .Controls

If ctl.Tag = "PK" Then

MyField = ctl.Name

MyKey = ctl

Exit For

End If

Next ctl

For Each ctl In .Controls

' inspect only data-bound controls

Select Case ctl.ControlType

Case acTextBox, acComboBox, acCheckBox

If Nz(ctl.ControlSource, "") > "" Then

' if changed, record both old & new values

If Nz(ctl.OldValue, "") <> Nz(ctl, "") Then

rs.AddNew

rs!FormName = .Name

rs!MyTable = MyTable

rs!MyField = MyField

rs!MyKey = MyKey

rs!ChangedOn = Now()

rs!FieldName = ctl.Name

If ctl.ControlType = acCheckBox Then

rs!Field_OldValue = YesOrNo(ctl.OldValue)

rs!Field_NewValue = YesOrNo(ctl)

Else

rs!Field_OldValue = Left(Nz(ctl.OldValue, ""),
255)

rs!Field_NewValue = Left(Nz(ctl, ""), 255)

End If

rs!UserChanged = UserName()

rs!CompChanged = CompName()

rs!Update <-- THIS IS WHERE THE DEBUGGER
TAKES ME

End If

End If

End Select

Next ctl

End With

rs.Close

Set rs = Nothing

Set db = Nothing

End Sub



Private Function YesOrNo(v) As String

Select Case v

Case -1

YesOrNo = "Yes"

Case 0

YesOrNo = "No"

End Select

End Function


Use rs.Update -- it's a method.
 
S

Sylvain Lafontaine

There are many possible cause for error 3146. The first thing to do would
be to show the full error message. Look for On Error Goto and display the
full error message in a window.

The second step would be verify if a real primary key exists on the tables -
it's often missed by Upsizing Wizard and if the data have been written or
not on the SQL-Server.

Finally, as you are using unbound forms; using ADO - along with the latest
OLEDB Provider for SQL-Server; ie., the Native OLEDB Provider for SQL-Server
2008, will work with 2000 and 2005 too - instead of DAO won't be a bad idea.
There is no point of using DAO if you don't use bound forms.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
 
D

deadtrees via AccessMonster.com

I found the problem, it's the way that the primary key is created in SQL as
opposed to Access. I'm going to start a new thread in the SQL Server forum.
Thanks to all for the suggestions.



Sylvain said:
There are many possible cause for error 3146. The first thing to do would
be to show the full error message. Look for On Error Goto and display the
full error message in a window.

The second step would be verify if a real primary key exists on the tables -
it's often missed by Upsizing Wizard and if the data have been written or
not on the SQL-Server.

Finally, as you are using unbound forms; using ADO - along with the latest
OLEDB Provider for SQL-Server; ie., the Native OLEDB Provider for SQL-Server
2008, will work with 2000 and 2005 too - instead of DAO won't be a bad idea.
There is no point of using DAO if you don't use bound forms.
I upsized to SQL, linked all the tables and am testing each field. My db
is
[quoted text clipped - 141 lines]
End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top