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 : )
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,702
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
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,702
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
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,702
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,095,782
Messages
5,446,469
Members
405,403
Latest member
horace james

This Week's Hot Topics

Top