VBA to get data from database.

[DHolcombe]

hi,

I am trying to use an excel vba program to get data from a database and then use this data in my program. But i have no clue about where to start.

Can someone help? I know this is kinda vague but i am not sure exactly what i need to know. Not in any of the books that i have on excel vba.

 

[Gary McMaster]

Excel has some built in functionality to query database tables. Look on the "Data" menu for a start.

Here is some info from Microsoft:

http://office.microsoft.com/en-us/excel/ha100996641033.aspx
http://office.microsoft.com/en-us/ork2000/HA011379381033.aspx?pid=CH011492571033

Once you decide which technology you want to use, search this forum for some of the "buzzwords" you will see in the Microsoft pages above. "MSQuery" or "ODBC", for example, will return hundreds of threads on this topic.

Hope this helps.

Gary

 

[DHolcombe]

thanks. I take a look at the material.

 

[xenou]

There's probably many technologies for Excel to work with databases - the menu already gives you connectivity tools relying on ODBC (I think), which boils down to MSQuery. Zapawa's book is best on this topic. . Its very easy to create an ODBC data source for Excel and once you do its re-usable. Another option is using ADO which is something you can create connections with using VBA - the code is not very hard to learn, and the connections are very clean and fast. The Wrox book on Excel VBA Programming has a good chapter on ADO. Also a good site for ADO is at Erlandsen Consulting . I believe there's also some third party tools written for this kind of stuff - who knows, you may like to work through such a tool.

There's actually a lot on the web but its sometimes hard to find an example exactly as you need - so just post here as you start to explore.

 

[DHolcombe]

yes, it has been hard to find. Especially for me since i am a novice when it comes to databases and such. I can program in VBA ok.....but i wish there was a "how to connect to databases in excel vba for dummies" somewhere. I have read snipets about ODB something or other and ADO something or other but i have no clue what they are.

thanks i will look at your suggestions as well.

note: my goal is to write some code in excel vba which will allow me to connect to a database and retrieve data. I am doing this for a family friends business. I hope to learn a bit along the way.

 

[xenou]

What kind of database? It is a big world of data-rich applications, but almost always it boils down:

1) Create a connection
2) Do what you want (run queries, update tables, etc. etc.)
3) Close the connection (if it's still open)

Usually this information is a chapter in one of your books. There are entire books on the subject (or close to it) - such as one by the title of integrating Excel and Access.

Further Resources(a very short list):

**If you don't know SQL then learn SQL first (at least the basics)**

MSQuery help:
tushar-mehta.com
dailydoseofexcel.com

ADO help:
the erlandsen link in a previous post
check out the ado page at w3schools.com
you'll find lots just googling Excel ADO

Also, Access might be another option for some of your work ...

 

[DHolcombe]

My first goal is to figure out how to establish a connection. Then verify that i did establish a connection.

2nd: See if i can run the query that someone has written for me. Or do i need to modify it given that i am quering from VBA.

I am still working on the first part. How to establish a connection.

 

[xenou]

Have you decided if you wish to pursue:

1) MSQuery (this is inbuilt in Excel menus under data connections - part of the process is using the wizards which help you create a "datasource" profile).
2) or ADO (this is done via VBA code)
3) or perhaps other options ( such as http://www.sqldrill.com )

You have not said what type of database you are using.

 

[DHolcombe]

hi,

To be honest. I am not sure of the "type" of database. I can ask.

What i do know.

1) I will need to use excel vba code to query / pull data from the data base.

2) I have an actual SQL query that someone wrote for me that pulls data from the database exactly how i want it..........but i dont know if its tranferable directly to VBA.

 

[Blade Hunter]

Here:

Sub GetData()
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim sqlText As String
Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet
Dim X As Long
    Set Data = Sheets("Results") '<--- Change this to the sheet you want the results on
    Data.Select
    Cells.ClearContents
    Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=DATABASE;USER ID=User1;PASSWORD=Pwd1"   '<--- Update these details, change the providor if needed
    Cmd.ActiveConnection = Conn
    Cmd.CommandType = adCmdText
    sqlText = "SELECT * FROM SYSOBJECTS" '<--- Put your SQL Query in here
    Cmd.CommandText = sqlText
    Set RS = Cmd.Execute
    For X = 1 To RS.Fields.Count
        Data.Cells(1, X) = RS.Fields(X - 1).Name
    Next
    
    If RS.RecordCount < Rows.Count Then
        Data.Range("A2").CopyFromRecordset RS
    Else
        Do While Not RS.EOF
           Row = Row + 1
           For Findex = 0 To RS.Fields.Count - 1
             If Row >= Rows.Count - 50 Then
                Exit For
             End If
             Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
           Next Findex
           RS.MoveNext
        Loop
    End If
    Cells.EntireColumn.AutoFit
End Sub