Accessing data in SQL Server via VBA

yassem

New Member
Joined
Jul 20, 2012
Messages
6
I am currently writing aset of macros thatare supposed to get data from an SQL server, paste it in two sheets and then mash it up and send. Point 2. and 3. is easy but I have no experience with SQL whatsoever, hence thequestion.

I've read several other threads on the subject on this forum and elsewhere, but none of them have given my a desicive sollution.

From what I understand to connect I'd need Provider, Catalog, Data source as well as UserID and Passwor, let's say I now all of these : )

My SQL is as follows:
Code:
SELECT t.ni
a.namei
a.surrnamei
b.cityi
b.streeti
c.numberi
c.date
FROM bwe.ki
where date=

and simillar for the other table, how am I supposed to write it?

I would greatly appreciate a swift anwser and thanks in advance : )
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,715
Something like this, it will put the results of the query in sheet1. You will need to set a reference to Microsoft AcitveX Data Objects x.x Library
Code:
Sub ExtractData()Dim cn  As ADODB.Connection
Dim rs  As ADODB.Recordset
Dim sql As String


Set cn = New ADODB.Connection


cn.Open "Provider = sqloledb;" & _
            "Data Source=xxx;" & _
            "Initial Catalog=xxx;" & _
            "User ID=xxx;" & _
            "Password=xxx;"""


sql = "SELECT t.ni, a.namei, a.surrnamei, b.cityi, b.streeti, c.numberi, c.Date "
sql = sql & "FROM bwe.ki "
sql = sql & "where Date = '2012/07/20';'"


Set rs = New ADODB.Recordset


rs.Open sql, cn


Sheets("sheet1").Cells(2, 1).CopyFromRecordset rs


rs.Close
cn.Close


End Sub
 

yassem

New Member
Joined
Jul 20, 2012
Messages
6
Wow, thanks a lot mate. That solves in one reply almost all my problems : )
Right now I only have to figure out what to put in "Provider" (or whether it stays the same), "data source" and "initial catalog". I have something in the form of PROTOCOL= ; HOST= ; PORT= as address and SID= (server id I presume?)
Still thank you so much as it brings me much closer to the sollution

P.S.
I am so underqualified for making this kind of stuff : D
 

yassem

New Member
Joined
Jul 20, 2012
Messages
6
And of course the worst questions of all, what do I need to download for that to work? I read something about ODBC etc., I'm working with MS Excel 2007 and 2010
 

yassem

New Member
Joined
Jul 20, 2012
Messages
6

ADVERTISEMENT

And of course the worst questions of all, what do I need to download for that to work? I read something about ODBC etc., I'm working with MS Excel 2007 and 2010

as I keep on getting
Compile error:
User-defined type not defined
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,715
Nothing usually. You'll need a driver to connect to the datasource but there's usually something already installed - I've used
sqloledb a lot without installing anything, so providor can probably stay the same.

Without seeing what info you've actually got it's difficult to comment, have a look here it takes you through building a connection string:
http://weblogs.sqlteam.com/dang/archive/2011/07/10/sql-server-connection-strings.aspx

 

yassem

New Member
Joined
Jul 20, 2012
Messages
6

ADVERTISEMENT

ok, so I managed to pass that error but still don't what to input there : D
 

yassem

New Member
Joined
Jul 20, 2012
Messages
6
I am reading for the second time right now, but I just don't know this sqlserver name I supose. I have some guesses, things that look like names, addresses etc. but none of them works in the code. I guess there is not really anything I can ask of you anymore : D
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,715
typically they can look like a domain name with a slash on the end then the instance and port of the db
 

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,746
Members
418,149
Latest member
amamiche67

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top