Solved: Query within Excel to SQL server with VBA Code

[scaat]

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:

[stanl]

It appears you do not have the SQL Server Provider on your PC at work. You can change the connection string to use the SQL Server Driver but your best bet is to download the latest copy of MDAC.

[scaat]

It appears you do not have the SQL Server Provider on your PC at work. You can change the connection string to use the SQL Server Driver but your best bet is to download the latest copy of MDAC.

actually, this is the same PC which I use in home and work (I am using a laptop and connecting to work network from home via OpenVPN)

I just couldn't figured out why it did not work on the same PC. By the way, I have tried to connect to it from home again, and it did not work even from there, where it worked in the first place. Therefore, I think problem is not related to the location, there is another problem at somewhere else.

I can install and download the data access components, but I suppose I will have to change the code as well. Can you give me an idea what needs to change on my code? Only the PROVIDER string?

[Jan Karel Pieterse]

Can you import the data manually using Data, Get external data, New database query?
If so, you can access the Connection property of the querytable object to extract the proper connection string.

[scaat]

Can you import the data manually using Data, Get external data, New database query?
If so, you can access the Connection property of the querytable object to extract the proper connection string.

Yes, I can import data manually from the same SQL server with Data Connection Wizard within Excel. That was amazingly simple and worked like a charm, thanks :beerchug: