Run VBA function on linked tables in SQL database

  • Thread starter Thread starter Dale
  • Start date Start date
D

Dale

Hi all...is this even possible. I have a stored vba function in access 97,
that sequentially numbers each group of records by comparing two recordsets
against one to the other. Is it possible to link to identically named
tables in an sql database and run the function against the linked
tables...so far I haven't had any luck but before I expend a lot of time on
this I'd appreciate knowing if its even possible.

Otherwise its off to another newsgroup to figure out how to convert this
function to SQL!

Thanks
 
Hi,
normally it should works with linked table also, you probably need to adjust
it a bit. do you get any error when you run it? maybe try to post it here
 
Thanks Alex
I'm getting the error 3197 in access 2000, access 97 just doesn't like it...

Sub NEnc()
Dim dbs As DAO.Database, rstEncounters As DAO.Recordset, rstPatients As
DAO.Recordset
Dim strSQL As String, strSQL2 As String, strFltSQL As String, strStart As
Double, strEnd As Double, lngCount As Long
On Error GoTo ErrExit
strStart = Now()
Set dbs = CurrentDb
strSQL = "SELECT tblEncounters.PATSYS, tblEncounters.c_date,
tblEncounters.ENCNum " _
& "FROM tblEncounters ORDER BY tblEncounters.Patsys,
tblEncounters.c_date"
strSQL2 = "Select tblPatients.Patsys From tblPatients Order by
tblPatients.Patsys"
Set rstEncounters = dbs.OpenRecordset(strSQL, dbOpenDynamic)
Set rstPatients = dbs.OpenRecordset(strSQL2, dbOpenDynamic)
rstPatients.MoveFirst

Do While Not rstPatients.EOF
lngCount = 1
Do While rstPatients!Patsys <> rstEncounters!Patsys
With rstEncounters
.Edit
!encnum = lngCount
.Update
.MoveNext
lngCount = lngCount + 1
Debug.Print lngCount
End With
Loop
rstPatients.MoveNext
Loop
ErrExit:
rstEncounters.Close
rstPatients.Close
Set dbs = Nothing
strEnd = Now()
MsgBox "Operation completed: " & Format(DateDiff("n", strStart, strEnd),
"###0.0000") & " mins"
End Sub
 
Hi,
in general code looks ok. if you have a trigger in table - first make sure
it does not produce this error. then make sure you installed latest jet
service pack. and try to add a timestamp field to tblEncounters table

HTH
 
Thanks Alex...being new to MySQL there was a bit of learning curve with how
it interacts with Access, after reinstalling the ODBC drivers...I was able
to get both versions of Access to do what I need done linked to a MySQL
backend...of course there was another issue of my production environment not
being in sync with my test environment...sigh...

But thanks to your help...I persevered...
Thanks again
 
Back
Top