Accessing data in SQL Server via VBA

[yassem]

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:

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

 

[Kyle123]

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

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]

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]

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]

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]

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]

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

 

[Kyle123]

Did you read the link I posted?

 

[yassem]

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]

typically they can look like a domain name with a slash on the end then the instance and port of the db