ADODB Connection String

**iancallaghan87** · 12-30-2008, 11:21 AM

All,

I have searched the Internet, and this forum, and have tried soo many attempts at this, however i am having no luck.

I am trying to call an INSERT stored Procedure, from VBA6.5 in Excel to SQLServer Express 2005.

I have created an ODBC connection called DevOH, and using the following few lines a can retrive data.
I have replaced my userID with 'aa' and the workstationID with 'bb'

##################################################

Dim qt As QueryTable
Dim sqlstring As String
Dim connString As String
sqlstring = "SELECT View1.Column1, View1.Column2 FROM View1"
connString = "ODBC;DSN=DevOH;Description=Development Overheads;UID=aa;APP=Microsoft® Query;WSID=bb;DATABASE=Dev OH;Network=DBNMPNTW"

With ActiveSheet.QueryTables.Add(Connection:=connString, Destination:=Range("A1"), Sql:=sqlstring)
.Refresh False
End With

###################################################

The above works fine,

I am now having problems trying to execute the INSERT Procedure, I have the code below, again have replaced my Workstation(Express Server) with 'bb' for consistency.

###################################################
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim connString As String

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command

connString = "Driver={SQLServer};Server=bb\IANSDB;Database=Dev OH;Trusted_Connection=True;"

cn.Open connString
cmd.ActiveConnection = cn
cmd.CommandText = "IC_InsertIntoTable1"
cmd.CommandType = adCmdStoredProc

cmd.Parameters("@Col1") = Range("B2").Value
cmd.Parameters("@Col2") = Range("B3").Value

cmd.Execute

cn.Close
###################################################

The error message i recieve is

#Run-time error '-2147467259(80004005)': Data source name not found and no default driver specified#

When i hit debug it's on the cn.Open connString line, i just can't seem to format my connection string properly, and help or advice is greatly appreciated.

Ian