Access talk to SQL server 2000

[stevens]

I am trying to connect from an Access 2000 form to an SQL server 2000
database using ADO and the following code:
Private Sub cmdGetM2MData_Click()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.Provider = "SQL Server"
cn.ConnectionString = "DSN = M2MSQL.dsn"

cn.Open
Set rs = New ADODB.Recordset
rs.Open "dbo_imbomm", cn, adOpenForwardOnly
rs.MoveFirst
Me.txtfpartno = rs!fpartno
Me.txtfcpartrev = rs!fcpartrev
End Sub

It compiles ok, but when executed, I always get the same error 3706
"Provider cannot be found. It may not be properly installed"

The error always occurs and stops on the cn.Open statement. I have created
the system DSN and testing shows it can successfully connect to the data
source. I have updated the MDAC on my Windows 2000 Pro workstation to 2.8.
Has anyone run across this problem and how do I solve it?
Thanks
Steve S

 

[Brendan Reynolds]

If you're using a DSN, you are not using the SQL Server provider, you're
using the ODBC provider.

The easiest way to get a connection string right is to use the Universal
Data Link (UDL) dialog box. In recent versions of Windows there's no direct
way to created a new UDL file, so just create a new text file and change the
extension from txt to udl. When you double click the file, Windows will open
the Universal Data Link dialog box, which you can use to point and click
your way to the data source. When you've created and tested the UDL (the
dialog has a Test Connection button), save it, then open it in Notepad (it's
a plain text file) and copy and paste the connection string into your code.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Mike Labosh]

I have written a little (24 KB) utility that gives you the Data Link
Properties Dialog to setup the connection, and then puts the correct ADO
connection string in a text box for you to copy to your clipboard. It does
not support ADO.NET connection strings. If anyone wants it email me:

MLabosh at Hotmail dot Com

--
Peace & happy computing,

Mike Labosh, MCSD

Feed the children!
Save the whales!
Free the mallocs!

 

[stevens]

Thanks Brendan, that worked like a champ!

If you're using a DSN, you are not using the SQL Server provider, you're
using the ODBC provider.

The easiest way to get a connection string right is to use the Universal
Data Link (UDL) dialog box. In recent versions of Windows there's no direct
way to created a new UDL file, so just create a new text file and change the
extension from txt to udl. When you double click the file, Windows will open
the Universal Data Link dialog box, which you can use to point and click
your way to the data source. When you've created and tested the UDL (the
dialog has a Test Connection button), save it, then open it in Notepad (it's
a plain text file) and copy and paste the connection string into your code.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.