Connection string - ODBC failure

[Halliarse]

Help!!

I have an issue with a connection string which apears to work right up until I try to access the database!

I am using VBA in Access 2010, with SQL 2008 and SQL NAtive Driver 10.0

My code is as below, both the variables bConnect and cmdDimensionsConnect are true.

Any help will be very gratefully received!

Public Function cmdDimensionsConnect(bConnect As Boolean) As Boolean

On Error GoTo Conn_Error

'Initialise Connection Variables
Dim Response As String, strServer As String, strUser As String, strPassword As String, strCatalog As String, strConnectionString

'Assign Default Values
strServer = "*******"
strUser = "sa"
strPassword = "********"
strCatalog = "DimensionsLiveAccounts"
strConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;Data Source=" & strServer & ";User ID=" & strUser & ";Password=" & strPassword & ";Initial Catalog=" & strCatalog & ";Data Provider=SQLOLEDB.1"


If bConnect = True Then
'Attempt Connection
Set oDimensionsConn = New ADODB.Connection
With oDimensionsConn
.ConnectionString = strConnectionString
.CursorLocation = adUseServer
.Open
cmdDimensionsConnect = True
End With
Else
'Attempt Close COnnection
oDimensionsConn.Close
cmdDimensionsConnect = True
End If

Conn_Exit:
Response = MsgBox(bConnect, vbYesNo)
Response = MsgBox(cmdDimensionsConnect, vbYesNo) Exit Function

Conn_Error:
cmdDimensionsConnect = False
Resume Conn_Exit

End Function



Set rsCustomers = CurrentDb.OpenRecordset("tblCustomers") - *** It's this line that falls over with the ODBC connection failure.

 

[PHV]

How is your cmdDimensionsConnect function related to tblCustomers ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vbajock]

I don't get the "Provider=SQLOLEDB.1" and the "DATA Provider=SQLOLEDB.1" in the same string.