SQL Server and VBA

  • Thread starter Thread starter Waterman
  • Start date Start date
W

Waterman

I recently migrated the tables from an Access database to
SQL Server, and still use Access as the front end. What
I am discovering is that not all of my VBA works now.
For instance, the following routine gets hung
on ".Update".

Sub UpdateThis ()
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblTransactions",
dbOpenDynaset)

With rst
.AddNew
!CallID = Me![call number]
!MemberID = Me![MemID]
!MemberLastName = Me![LastName]
!MemberFirstName = Me![FirstName]
!MemberDepCode = Me![DepCode]
!TransReason = Me![CallReason]
!Notes = Me![Notes]
!HandledBy = Me![Handled By]
!DateReceived = Me![Date Received]

.Update

[Forms]![frmCallEntry]![subfrmEntry].Requery

Me![MemID] = Null
Me![LastName] = Null
Me![FirstName] = Null
Me![DepCode] = Null
Me![CallReason] = Null
Me![Notes] = Null

Me![MemID].SetFocus

End With

rst.Close

End Sub

Is it because ".Update" only works for Access and not SQL
Server?
 
Waterman said:
I recently migrated the tables from an Access database to
SQL Server, and still use Access as the front end. What
I am discovering is that not all of my VBA works now.
For instance, the following routine gets hung
on ".Update".

Sub UpdateThis ()
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblTransactions",
dbOpenDynaset)

With rst
.AddNew
!CallID = Me![call number]
!MemberID = Me![MemID]
!MemberLastName = Me![LastName]
!MemberFirstName = Me![FirstName]
!MemberDepCode = Me![DepCode]
!TransReason = Me![CallReason]
!Notes = Me![Notes]
!HandledBy = Me![Handled By]
!DateReceived = Me![Date Received]

.Update

[Forms]![frmCallEntry]![subfrmEntry].Requery

Me![MemID] = Null
Me![LastName] = Null
Me![FirstName] = Null
Me![DepCode] = Null
Me![CallReason] = Null
Me![Notes] = Null

Me![MemID].SetFocus

End With

rst.Close

End Sub

Is it because ".Update" only works for Access and not SQL
Server?
Click to expand...

No, but there are various circumstances that may render a recordset on a
linked SQL-Server table nonupdatable. What do you mean by 'gets hung on
".Update"'? Is there no error message? If there is an error message,
what is it? I found in my own upsizing that I had to add the
dbSeeChanges option to a number of OpenRecordset calls.

Also, what version of Access are you using? If you're using Access 2000
or later, have you resolved the references conflict between DAO and ADO?
(I assume you have, or you'd get an error message earlier, but you never
know.)
 
Thanks, Dirk, for your reply. I am using Access 2002,
and the error I am receiving is #3146 'ODBC Failed',
which is odd since I am logged in and connected to the
server, and able to manually input data into the database
with no issues. It is simply when I try to use the
routine (as shown stated in the previous post) that I
have this issue.

I will have to investigate the ADO/DAO conflicts a bit
more to determine if that is the source of my issue.
 
Thanks, Dirk, for your reply. I am using Access 2002,
and the error I am receiving is #3146 'ODBC Failed',
which is odd since I am logged in and connected to the
server, and able to manually input data into the database
with no issues. It is simply when I try to use the
routine (as shown stated in the previous post) that I
have this issue.

I will have to investigate the ADO/DAO conflicts a bit
more to determine if that is the source of my issue.
Click to expand...

Almost anything that goes wrong when you're working with an ODBC
database is reported as error 3146, "ODBC call failed". If you want to
know what *really* happened, you have to examine the DBEngine.Errors
collection. Go into debug mode immediately after the error has occurred
and enter this in the Immediate window:

for i = 0 to DBEngine.Errors.Count - 1 : ?DBEngine.Errors(i): next i

That should show you the series of errors as they were raised in turn.
 
Back
Top