VBA - Create Pass Thru Query without ODBC DSN??

[stephenmbell]

Is it possible to programatically create a pass thru query that connects to SQL server without having a DSN on that machine?

I can't seem to get it to work.

Thanks
sb

 

[vbajock]

Google "vba sql server dsn-less connection" you will get back all kinds of sample code

 

[stephenmbell]

Thank you.

I know how to create a connection via ADO.

The end result that I am looking for is to simply open a "table view" result set in access. Basically, click a button, open a query to be displayed to the end user. I don't know how to do this easily with ADO.

Is this possible?

Thanks
sb

 

[dhookom]

I typically create and save a generic pass-through query and use a little dao code to set the connection and SQL properties.

Sub UpdatePTConnection(pstrPTQuery As String, pstrServer As String, pstrDatabase As String, _
        pstrUID As String, pstrPwd As String)
    'ODBC;Driver={SQL Server};Server=XXXXX,3001;Database=xxx;Uid=xxx;Pwd=xxx
    Dim strConnection As String
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef

    strConnection = "ODBC;Driver={SQL Server}" & _
        ";Server=" & pstrServer & _
        ";Database=" & pstrDatabase & _
        ";Uid=" & pstrUID & _
        ";Pwd=" & pstrPwd

    Set db = CurrentDb
    db.QueryDefs.Refresh
    Set qd = db.QueryDefs(pstrPTQuery)
    qd.Connect = strConnection
    db.QueryDefs.Refresh
    Set qd = Nothing
    Set db = Nothing
       
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[stephenmbell]

That looks like it may work. What happens if the database is shared? And furthermore, what happens if it is shared and more than 1 person goes to run this process at once?

Thanks for the response
sb

 

[dhookom]

This code should be running in the front-end which is not shared. Everyone has their own copy. If not, you may clobber each other.

Duane
Hook'D on Access
MS Access MVP