Excel VBA Script for DB Connection

Question

I'm new to VBA and need a few pointers on writing a script that opens a connection from an Excel file to a SQL Server database. I'm referencing the VBA language reference at https://msdn.microsoft.com/en-us/library/office/gg264383.aspx and can't find information on how this is done. The following is what I have managed to scrape from an macro-enabled excel file that I found floating around:

Sub CopyData()
    Dim sSQL As String
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[Server Name];Initial Catalog=[Catalog];"
    End With
End Sub

When I run the script I receive an error 'User-defined type not defined'. And the line Dim rs AS ADODB.Recordset is highlighted. So my understanding here is that ADODB.Recordset has not been defined as a class yet...but I thought this was an built-in class? And assuming this is not a built-in class, I can't find any where in the source code where this class would have been defined. My end goal here is to run a SQL query based on input into the excel file and output the results of the query into adjacent columns. So my questions:

1) Where can I find Microsoft documentations on database connection objects? 2) Perhaps more of a SQL question, but how should I format the connection string?

I'm not sure which library, but you need to add a library by going to Tools / References. Should be one of those starting with "Microsoft".. — PKatona
– PKatona, Commented Apr 4, 2016 at 21:21
@TimWilliams Nice! This appears to be what I need. Thank you — BenWS
– BenWS, Commented Apr 5, 2016 at 13:05

Karthick Gunasekaran · Accepted Answer · 2016-04-05 15:01:26Z

1

Dim con As ADODB.Connection
Dim rec As ADODB.Recordset
Set con = New ADODB.Connection
Set rec = New ADODB.Recordset
rec.CursorLocation = adUseClient
con.Open ("Provider=SQLOLEDB;Data Source=ip;Initial Catalog=dbname;user ID=userid; password=password;")
Query = "SELECT unique_Id FROM data group by unique_Id;"
rec.Open Query, con
Do While rec.EOF() = False
    'do events
rec.MoveNext
Loop

Add Microsoft ActiveX Data Objects 2.8 Library as a Reference

answered Apr 5, 2016 at 15:01

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA Script for DB Connection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related