CODEWITHSUNDEEP
sql-servervbaexcel
BenWS
BenWS

Reputation: 531

Excel VBA Script for DB Connection

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?

Upvotes: 0

Views: 5969

Answers (1)

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

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

Upvotes: 1

Related Questions