code when backend is SQL server

[alvin Kuiper]

Hi
I have use this code in access
username = ReturnUserName
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
'Set rs = db.OpenRecordset("undervisere")
strSQL = "SELECT * FROM undervisere WHERE initialer like '" & username
& "' "
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

But after splitting my db so the backend is a SQL server this code dosn't
work anymore, can someone help?

Regards
alvin

 

[Stefan Hoffmann]

hi Alvin,

But after splitting my db so the backend is a SQL server this code dosn't
work anymore, can someone help?

Are using now an ADP or still a MDB with linked tables?


mfG
--> stefan <--

 

[alvin Kuiper]

Hi stefan
I use mdb with linked table

Regards
alvin



"Stefan Hoffmann" skrev:

 

[Stefan Hoffmann]

hi Alvin,

I use mdb with linked table Ok.

username = ReturnUserName
Dim db As Database
Dim rs As Recordset

Use

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

Set db = CurrentDb()
'Set rs = db.OpenRecordset("undervisere")
strSQL = "SELECT * FROM undervisere WHERE initialer like '" & username
& "' "
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

But after splitting my db so the backend is a SQL server this code dosn't
work anymore, can someone help?

What error message do you get?

If your linked tables have the same names as before, check for the nasty
leading "dbo_", then both ways should work.


mfG
--> stefan <--

 

[alvin Kuiper]

Hi Stefan
in this line
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
I get this message
Runtime error 3146
ODBC the call didn't succes

regards
alvin


"Stefan Hoffmann" skrev:

 

[Stefan Hoffmann]

hi Alvin,

Hi Stefan
in this line
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
I get this message
Runtime error 3146
ODBC the call didn't succes

Check if you can open the table directly in the database window. Check
the permissions set on the SQL Server. Also check the credentials you
are using for your linked table.

Furthermore, take a look at the archive:

http://groups.google.de/groups?hs=d...&btnG=Suche&lr=&ie=UTF-8&oe=UTF-8&sa=N&tab=wg


mfG
--> stefan <--

 

[alvin Kuiper]

Hi Stefan
I have done it this way it works
Dim Cnxn As ADODB.Connection

Dim strCnxn As String
Set Cnxn = New ADODB.Connection
strCnxn = "Provider='sqloledb';Data Source='kontor';" & _
"Initial Catalog='pcgruppen';UID=jkjkjk;password=pass;;"
Cnxn.Open strCnxn
Set rs = New ADODB.Recordset
strSQL = "undervisere"
rs.Open strSQL, Cnxn, adOpenKeyset, adLockOptimistic, adCmdTable

Thanks
For the help
Alvin


"Stefan Hoffmann" skrev: