Connect to SQL Server through VBA

[gregger20]

Hello,
Could someone help me connect to and execute a query to Sql Server with VBA?

Thanks alot,

Gregger20

 

[genomon]

Are you using linked tables in Access? If so, just use the query builder to create a pass-through query....
Therwise here is code we use to accomplish the task - so sorry I am unable to credit the original author:

Option Compare Database
Option Explicit

Public strODBCsql As String

Sub odbcdirect_connect(dare_database As String)

    Dim wrkODBC As Workspace
    Dim conWrk As Connection
    Dim rstSetup As Recordset
    Dim dbsdb As Database
    Dim strconnect As String
    Dim strstatusmsg As String
    Dim strstatus As String
          ' EXAMPLE: strODBCsql = "execute usp_update_newsletter_tables '02-Jan-2001'"
          ' get ODBC connect string
    Set dbsdb = CurrentDb
    Set rstSetup = dbsdb.OpenRecordset("tbl_odbcdirect")   'need to get the setup table to a variable or constant
          ' MsgBox dbsdb.RecordsAffected
    strconnect = rstSetup!SQLconnect & dare_database
    rstSetup.Close
    
    Set rstSetup = Nothing
    Set wrkODBC = CreateWorkspace("DARE", "", "", dbUseODBC)
    Set conWrk = wrkODBC.OpenConnection("conDARE", , , strconnect)
        ' new as of 4/9/2001 = no ODBC timeout
    conWrk.QueryTimeout = 0
        ' DoCmd.Hourglass
    Screen.MousePointer = 11
    strstatusmsg = strODBCsql
    strstatus = SysCmd(acSysCmdSetStatus, "Executing: " & strstatusmsg)
    conWrk.Execute strODBCsql
        ' To use following code ad async option: conWrk.Execute strODBCsql, dbRunAsync
        ' While conWrk.StillExecuting
        ' strstatus = SysCmd(acSysCmdSetStatus, "still Executing: " & strstatusmsg)
        ' Wend
    Screen.MousePointer = 0
    strstatus = SysCmd(acSysCmdSetStatus, "Done")
        ' strstatus = (acSysCmdClearStatus)
    conWrk.Close
    wrkODBC.Close
    
    Set dbsdb = Nothing
    Set conWrk = Nothing
    Set wrkODBC = Nothing
    
End Sub

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[genomon]

OOPS! The contents of the recordset will be helpful!

ODBC;UID=yourID;PWD=YourPW;FILEDSN=YourDSN.dsn;DATABASE=

Where you supply the ID, PW, and DSN.

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[JerryKlmns]

gregger20

Keep this handy

http://www.connectionstrings.com