asingh
09-19-2008, 05:33 AM
Hi,
I have an excel sheet with data and VBA in it.
I am able to capture the data from my sheet, and now want to write it to a SQL 2005 Server Express system.
I though to do it via ADODB.
I am setting the connection string as:
crt_spss_conn = "Driver={SQL Server};" _
& "Server=" & sql_ip & ";" _
& "Database=" & sql_db_nm & ";" _
& "Uid=" & sql_usr_nm & ";" _
& "Pwd=" & sql_usr_pwd & ";"
where:
sql_ip is the server IP.
sql_db_nm is the data base name on the server.
Uid is the user name.
Pwd is the password.
I wrote the below mentioned code just to "test the connection"
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "SELECT * FROM tbl_rstrt_dt", crt_spss_conn
Close cnn
Set cnn = Nothing
Here tbl_rstrt_dt is would be the destination table. I am just running a normal select query on the destination table, to test, that I can connect to the destination table correctly.
I keep getting the error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
thanks a lot for the help,
regards,
asingh
I have an excel sheet with data and VBA in it.
I am able to capture the data from my sheet, and now want to write it to a SQL 2005 Server Express system.
I though to do it via ADODB.
I am setting the connection string as:
crt_spss_conn = "Driver={SQL Server};" _
& "Server=" & sql_ip & ";" _
& "Database=" & sql_db_nm & ";" _
& "Uid=" & sql_usr_nm & ";" _
& "Pwd=" & sql_usr_pwd & ";"
where:
sql_ip is the server IP.
sql_db_nm is the data base name on the server.
Uid is the user name.
Pwd is the password.
I wrote the below mentioned code just to "test the connection"
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "SELECT * FROM tbl_rstrt_dt", crt_spss_conn
Close cnn
Set cnn = Nothing
Here tbl_rstrt_dt is would be the destination table. I am just running a normal select query on the destination table, to test, that I can connect to the destination table correctly.
I keep getting the error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
thanks a lot for the help,
regards,
asingh