MS SQL+MSACCESS Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

MS SQL+MSACCESS Function

Author

Topic

MaSe
Starting Member

8 Posts

Posted - 2005-11-14 : 09:43:14

I have linked tables on my MS access, The tables are on a MS SQL server.
I made a function to calculate a price. The function works whem i have the tables in ms access in de *.MDB file.
I think that i am dowing something wrong whit the Set dbData = CurrentDb.
Or that de recordset is not the same that i am getting back...?

This is the function:

Public Function gettest(Vorm, Groep, Prijs) As Currency
On Error GoTo stoppen2

Dim dbData As Database
Dim rec As Recordset
Dim szSQL As String

Set dbData = CurrentDb

szSQL = "SELECT * FROM tblContractprijs WHERE (GroepID=" + str(Groep) + " AND ContractvormID=" + str(Vorm) + ");"

Set rec = dbData.OpenRecordset(szSQL)
gettest = 0

While Not rec.EOF
If (Prijs >= rec![Cataloguswaardebegin]) And (Prijs < rec![Cataloguswaardeeind]) Then
gettest = rec![Contractprijs]
End If
rec.MoveNext
Wend

rec.Close
Exit Function

stoppen2:
gettest = 1111
End Function

The function never gets past Set rec = dbData.OpenRecordset(szSQL)
Please can someone help me..

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2005-11-17 : 13:16:48

you don't actually say what the error message is but i am thinking it is in the following...

szSQL = "SELECT * FROM tblContractprijs WHERE (GroepID=" + str(Groep) + " AND ContractvormID=" + str(Vorm) + ");"

perhaps something like...

szSQL = "SELECT * FROM tblContractprijs WHERE (GroepID=" & chr(34) & str(Groep) & chr(34) & " AND ContractvormID=" & chr(34) & str(Vorm) & chr(34) & ")"

well i may be wrong but your set dbData=currentdb looks fine to me

====
Paul

MaSe
Starting Member

8 Posts

Posted - 2005-11-18 : 05:34:39

ow sorry here is the error:
Run-time error '3622'

You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

MaSe
Starting Member

8 Posts

Posted - 2005-11-18 : 07:26:34

Ah got it to work

Public Function gettest(Vorm, Groep, Prijs) As Currency
'On Error GoTo stoppen2

Dim dbData As Database
Dim rec As Recordset
Dim szSQL As String

Set dbData = CurrentDb

szSQL = "SELECT * FROM tblContractprijs WHERE (GroepID=" + str(Groep) + " AND ContractvormID=" + str(Vorm) + ");"

Set rec = dbData.OpenRecordset(szSQL, dbOpenDynaset, dbSeeChanges)
gettest = 0

While Not rec.EOF
If (Prijs >= rec![Cataloguswaardebegin]) And (Prijs < rec![Cataloguswaardeeind]) Then
gettest = rec![Contractprijs]
End If
rec.MoveNext
Wend

rec.Close
Exit Function

stoppen2:
gettest = 1111
End Function

Forgot to put in : dbOpenDynaset, dbSeeChange

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources