Access 2010 to SQL 2000

[texas1992]

I have an old access 2003 db (windows XP) that I am converting to access 2010 (windows 7). In the frontend I have a connection to an old SQL Server 2000 database. I need to recreate the connection from the access 2010 to the SQL 2000 server.

Here is my connection string:

OpenConnection("Export", dbDriverNoPrompt, True,
ODBC;provider=SQLOLEDB;SERVER=<servername>;DATABASE=<databasename>;UID=<userID>;PWD=<password>;DSN=''")

Any help that you can provide would be greatly appreciated.

Thanks.

 

[G37Sam]

This is what I use, DSN not needed

Dim cnxn As New ADODB.Connection
Dim rs As ADODB.Recordset

oConn.Open "Provider=sqloledb;" & _ 
           "Server=myServerName;" & _
           "Database=myDatabaseName;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"

Set rs = New ADODB.Recordset
rs.Open "tableName", cnxn

rs.Close
cnxn.Close

 

[texas1992]

Sam,
Thanks for your response. I tried it but unfortunately it didn't work. Do you have anyother suggestions?

 

[mdlueck]

I tried it but unfortunately it didn't work.

Could you please be more specific?

What connection technology is the Access FE using? You were provided an example of an ADO connection. Perhaps you are using DAO.LinkedTables or DAO.QueryDef objects, or or or... Access has many options. Your application could very well be using a mix of several types of objects which all need to know how to connect to the server.

 

[boblarson]

A good reference:
http://www.connectionstrings.com/sql-server

 

[Rx_]

The above site was great to get started. For SQL Native client - this worked for me. The Option=3 was difficult to find. It appears to Persist the connection.

40 DataBaseName = "MyDB"
50 strConnectionString = "ODBC;DRIVER=SQL Server Native Client 10.0;" & _
"SERVER=sqlInstance\DBServerInstance;DATABASE=" & DataBaseName & ";" & _
"UID=" & UID & ";" & _
"PWD=" & PWD & ";" & _
"Table=DBO." & sLocalName & ";Option=3;"
60 ModifiedRefreshDNSLess2 = strConnectionString