scaat
01-28-2010, 01:48 AM
Hello everybody. I had a little search on what I am looking for, but I am quite new on this forum and I could not find any answers.
I am trying to query some data from an MS-SQL database to excel. Actually, my main intention is to create an order form in Excel and fill in the necessary fields from relative fields in SQL database.
I have created a new module and wrote this code to import the data
Sub DataExtract()
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=192.168.1.101;INITIAL CATALOG=somedatabase;"
strConn = stConn & " User ID=userid1;Password=password1234"
cnPubs.Open strConn
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
With rsPubs
.ActiveConnection = cnPubs
.Open "SELECT * FROM TBLSTOKURM WHERE MAMUL_KODU = 'GA025 00002'"
Sheet1.Range("A1").CopyFromRecordset rsPubs
.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
End Sub
By the way, I have found this code from a microsoft support page and modified the connection and table information according to my needs.
When I run the code at the first time from my home via VPN, it worked like a charm. Than in the morning I come to the office, I tried to run the code and I had following error message:
[ODBC Driver Manager] Data source name not found and no default driver specified
The server I tried to reach (192.168.1.101) is on my office, where I tried to run the code and failed (really weird, huh?)
I am able to ping and remote administer the server from same IP address but somehow, code is not working. Can anyone tell me what is wrong with it? :think:
I am trying to query some data from an MS-SQL database to excel. Actually, my main intention is to create an order form in Excel and fill in the necessary fields from relative fields in SQL database.
I have created a new module and wrote this code to import the data
Sub DataExtract()
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=192.168.1.101;INITIAL CATALOG=somedatabase;"
strConn = stConn & " User ID=userid1;Password=password1234"
cnPubs.Open strConn
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
With rsPubs
.ActiveConnection = cnPubs
.Open "SELECT * FROM TBLSTOKURM WHERE MAMUL_KODU = 'GA025 00002'"
Sheet1.Range("A1").CopyFromRecordset rsPubs
.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
End Sub
By the way, I have found this code from a microsoft support page and modified the connection and table information according to my needs.
When I run the code at the first time from my home via VPN, it worked like a charm. Than in the morning I come to the office, I tried to run the code and I had following error message:
[ODBC Driver Manager] Data source name not found and no default driver specified
The server I tried to reach (192.168.1.101) is on my office, where I tried to run the code and failed (really weird, huh?)
I am able to ping and remote administer the server from same IP address but somehow, code is not working. Can anyone tell me what is wrong with it? :think: