Connecting to SQL Server 2005

[trackdaychamp]

Hi,

I am using VBA to connect my Access ADP file to SQL Server 2005 Enterprise Edition. Can anyone spot a glaring errors with the following code?

Thanks,

Mark

'Open the connection to the SQL Server DB
Dim cn As ADODB.Connection

Dim strServerName As String
Dim strDbName As String
Dim strProvider As String

strServerName = "DELOITTE-A583B4"
strDbName = "PMO_ADP_Single Connection"
strProvider = "sqloledb"



' Specify the OLE DB provider.
cn.Provider = strProvider

With cn

' Set SQLOLEDB connection properties.
.Properties("Data Source").Value = strServerName
.Properties("Initial Catalog").Value = strDbName
.Properties("User Name").Value = "PmoLogin"
.Properties("Password").Value = "1234"

End With


cn.Open

 

[boblarson]

It would appear you're missing an instantiation of the connection object:

'Open the connection to the SQL Server DB
Dim cn As ADODB.Connection

Dim strServerName As String
Dim strDbName As String
Dim strProvider As String

strServerName = "DELOITTE-A583B4"
strDbName = "PMO_ADP_Single Connection"
strProvider = "sqloledb"

Set cn = New ADODB.Connection


There may be other errors, but I haven't used ADO to connect to SQL normally (I've used it internally mostly), I may not have caught any others.

 

[trackdaychamp]

Thanks Bob!

Mark