PDA

View Full Version : Append data from EXCEL to SQL Server



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

Bob Phillips
09-19-2008, 06:22 AM
My CN string would like like this

"Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"

asingh
09-19-2008, 07:49 AM
thanks...XLD..will get to try this on Monday..when I reach my office system..!!

asingh
09-21-2008, 09:29 PM
Hi,

Still getting the same error, as I mentioned above..tried the new connection string..

By the way the SQL server is not on my system.it is at a remote location. So in the data source I am supplying the server IP...will this work..for remote connectivity.....?

Bob Phillips
09-22-2008, 12:09 AM
How about this?



crt_spss_conn.Open "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=" & sql_ip & ",1433;" & _
"Initial Catalog=" & sql_db_nm & ";" & _
"User ID=" & sql_usr_nm & ";" & _
"Password=" & sql_usr_pwd & ";"

asingh
09-22-2008, 12:38 AM
Okay...the tried the above mentioned...it attempted to connect..but gave an error:

[DBMSSOCN] General network error. Check your network documentation.

I know for sure that the server is up, just verified from a Remote Desktop connection. What could be the issue now..??

Bob Phillips
09-22-2008, 12:54 AM
This connection is assuming a firewall. Take a look at these articles

http://support.microsoft.com/default.aspx?kbid=238949 ,

http://support.microsoft.com/default.aspx?kbid=269882 and

http://support.microsoft.com/default.aspx?kbid=287932

asingh
09-22-2008, 05:55 AM
Am I behind a firewall..that is not letting me connect..or is the remote server behind a firewall..not letting me get through..?

Bob Phillips
09-22-2008, 06:04 AM
I don't know, I think you need to speak to your IT guys.