Connecting a Passthru Query with connection string

I am rusting in writing VBA code - need assistance in modifying the code below.  I have a pass thru query that needs to call the ODBC connection of the DSN name.  I have seen several examples of this code, however, I am unclear on how to modify it to run my pass thru query and accessing or establishing the correct connection.

You assistance is appreciated.
Karen

Public Sub append_new_messages_to_datamart_fact_table()
    Dim rs As ADODB.Recordset
    Dim qdef As QueryDef
    Dim lCt As Long
    Dim conn
    Dim DataConn
    Dim CmdSimpleSelect
    Dim MYSQL As String
   
Set DataConn = Server.CreateObject("ADODB.Connection")
Set CmdSimpleSelect = Server.CreateObject("ADODB.Recordset")
   ODBC;DSN=MySql_Datamart;SERVER=datamart;UID="";PWD=;

DataConn.Open "DSN=MySQl_Datmart"
'Database connection statements.
Set conn = Server.CreateObject("ADODB.Connection")
'conn.Open "DSN=MySQL_datamart"
'conn.Open "DSN=MySQL_datawarehouse"
'conn.Open "DSN=MySQL_aggr"
'conn.Open "DSN=MySQL_ost"
'conn.Open "DSN=MySQL_enterprise"
'conn.Open "DSN=MySQL_mcmds"

    'Set qdef = CurrentDb.QueryDefs("qry_datamart_etl_command")
   
   
MYSQL = (" INSERT INTO datamart.messages_denormalized" & _
" ( OriginalMessageID , System , TransportProvider , Shortcode , Carrier , `MIN` , Body , SystemTime , `InOut` )" & _
" SELECT SMSInboundMessageID , 'EMS' AS System , TP.Name as TransportProvider , SC.Name AS ShortCode ," & _
" C.Name as Carrier , P.MIN , CAST(`Data` AS CHAR CHARACTER SET utf8) Body ," & _
" FROM_UNIXTIME(M.CreationTime) AS Created , 'in' AS `InOut`" & _
" FROM enterprise.sms_inbound_messages M" & _
" LEFT JOIN enterprise.message_transport_providers TP ON M.MessageTransportProviderID = TP.MessageTransportProviderID" & _
" LEFT JOIN enterprise.shortcodes SC ON M.ShortcodeID = SC.ShortcodeID" & _
" LEFT JOIN enterprise.carriers C ON M.CarrierID = C.CarrierID" & _
" LEFT JOIN enterprise.min_profiles P ON M.MINProfileID = P.MINProfileID" & _
" WHERE M.SMSInboundMessageID > (" & _
"      SELECT MAX(OriginalMessageID)" & _
"      FROM datamart.messages_denormalized" & _
"      WHERE `InOut` = 'in' AND System = 'EMS' )")

Set qdef = CurrentDb.QueryDefs(MYSQL)

   ' Set rs = New ADODB.Recordset
   
   ' With rs
    '    .ActiveConnection = CurrentProject.Connection
    '    .CursorLocation = adUseClient
   '     .CursorType = adOpenForwardOnly
     '   .LockType = adLockReadOnly
       
     '   .Source = "SELECT [SQL] FROM etl_commands ORDER BY ord "
       
     '   .Open
       
        Do Until .EOF
            qdef.SQL = !SQL
            qdef.ReturnsRecords = False
            ' Debug.Print !SQL
            qdef.Execute
           
           
            .MoveNext
        Loop
       
        .Close
       
    End With

'Set rs = Nothing
'Set conn = Nothing
   
   
End Sub
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leigh PurvisDatabase DeveloperCommented:
Yeah - you've got some commented out ADO code there that you'll not use of course.
You just need the connect property for your query setting.

Could be as simple as

qdef.Connect = "ODBC;DSN=MySql_Datamart"
qdef.Execute
0
Karen SchaeferBI ANALYSTAuthor Commented:
I am confused are you saying to create a function that calls just the connection string.

qdef.Connect = "ODBC;DSN=MySql_Datamart"
qdef.Execute

if this is the case if errored out.

Here is my code:
Public Function ConnectDSN()
'===============================================================================================
'Error-handler inserted on 3/24/2006 at 12:09
'Created by: Karen Schaefer
'
'===============================================================================================
On Error GoTo ConnectDSN_Error
    Dim qdef As QueryDef


qdef.Connect = "ODBC;DSN=MySql_Datamart"
qdef.Execute
qdef.Connect = "ODBC;DSN=MySql_enterprise"
qdef.Execute


ConnectDSN_Exit:
Exit Function

ConnectDSN_Error:
MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & Error$, vbExclamation, "Access9db - ConDSN"
Resume ConnectDSN_Exit

End Function

What am I missing.

K
0
Leigh PurvisDatabase DeveloperCommented:
What was the error message? (Your DSN might not have enough detail to connect to the database by itself)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Karen SchaeferBI ANALYSTAuthor Commented:
Object variable  or With block not set unexpected Error -91

My DSN is set up as local host with passwords already established.

What am I missing.

Thanks,

Karen
0
Leigh PurvisDatabase DeveloperCommented:
Oh right yeah - I'd not really read your first lot of code and figured everything was really in place except the connect property...
OK then - let's see.

You're just wanting to execute an append on the server - so no desire for a permanent query definition.
Try calling the following - passing the SQL you want to execure to it.

Function fPassThrough(strSQL As String, strDSN)
   
    Dim db As Database
    Dim qdf As QueryDef
   
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("", strSQL)
   
    With qdf
        .ReturnsRecords = False
        .Connect = "ODBC;DSN=" & strDSN
        .Execute
        .Close
    End With
   
End Function


So in your examples perhaps something like

Dim MYSQL as string

MYSQL = (" INSERT INTO datamart.messages_denormalized..." 'etc - that big long one
fPassThrough MYSQL, "MySql_Datamart"
'or
fPassThrough MYSQL, "MySql_enterprise"


if you always use the same DSN you could just have that in as a constant in the function - rather than pass it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Is there a way to create the connect without using the long query def.

All I want to do is reestablish the ODBC connection to the MYSQL Database on open of my mdb.

Is there a simple way of doing this?

Karen
0
Leigh PurvisDatabase DeveloperCommented:
So you don't actually want a passthrough at all?
Just a persistant connection open to your server database??
0
Karen SchaeferBI ANALYSTAuthor Commented:
I want to on open of the database relink the MySql tables on the fly.  So that I can run the existing pass thru queries.

I want to be able to use a scheduler and run this database nightly, unfortuantely without a manual intervention of updateing the MySql tables it fails.  I was hoping there is a simple way to relink these tables via VBA Code.

Thanks,

Karen
0
Leigh PurvisDatabase DeveloperCommented:
Oh...
So you don't want to open a connection now?
You want to relink tables?
:-S

Are you sure?
;-)
0
Karen SchaeferBI ANALYSTAuthor Commented:
I have a database that I want to run at night and on activation of the database I want to make sure all MySQL table are available.  Whether that is with relinking or establishing the connection.  I was hoping for a simple solution, however everything I see on the web about DSN -Less connections or ODBC connections are complicated for MYSQL.  Which ever is the most reliable and will accomplish the task on a regular basis is the best.

Please feel free to make a recommendation.

Thanks for your time,

Karen
0
Leigh PurvisDatabase DeveloperCommented:
I'm not a mySQL user - so can't advize you on that score.

How do you normally link to the tables?
(You link to different sets of tables in different db's?)

Do you have a local table of the linked table names?

There needs to be some way of identifying what to create...
0
Karen SchaeferBI ANALYSTAuthor Commented:
Link table manager via OBDC DSN link  - it seems that the ODBC connect times out.

Thnks
K
0
Leigh PurvisDatabase DeveloperCommented:
An ODBC connection timeout will affect a particular action.
An update statement - or fetching an aweful lot of rows perhaps...

But are you saying that during linking the tables (even manually) you get a timeout?
0
Karen SchaeferBI ANALYSTAuthor Commented:
No not during manual - and I am assuming this is the issue why it is requiring me to manually updating the link to the particular tables.

k
0
Karen SchaeferBI ANALYSTAuthor Commented:
I am leaving for the night thanks for help hopefully there is a simple solution out there somewhere.

Have a great weekend.

K
0
Karen SchaeferBI ANALYSTAuthor Commented:
I am still looking for a good solution.

Thanks,

Karen
0
Karen SchaeferBI ANALYSTAuthor Commented:
This is what I have so far:  I am getting operation not allowed when object is closed

Please help:

Public Function RefreshLinks()
'===============================================================================================
'Error-handler inserted on 3/27/2006 at 14:39
'Created by: Karen Schaefer
'refresh MySql table links
'===============================================================================================
On Error GoTo RefreshLinks_Error

Dim conn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim sql As String

Set conn = New ADODB.Connection

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                       " SERVER=localhost;" & _
                       " DATABASE=DataMart;" & _
                       " UID=username;PWD=password; OPTION=35"
conn.Execute "CREATE TABLE MessageTemp (OriginalMessageID , System , TransportProvider , Shortcode , Carrier , `MIN` , Body , SystemTime , `InOut` )" & _
" SELECT OriginalMessageID , System , TransportProvider , Shortcode , Carrier , `MIN` , Body , SystemTime , `InOut` )" & _
" FROM Message_Denormilzed)"
conn.Close



RefreshLinks_Exit:
Exit Function

RefreshLinks_Error:
MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & Error$, vbExclamation, "Access9db - RefLin"
Resume RefreshLinks_Exit

End Function
0
Karen SchaeferBI ANALYSTAuthor Commented:
Your driver information should be specified in your DSN.
Try;
Public Function MySql_Initialization()
Dim myDB As DAO.Database
Dim strConnect As String
On Error GoTo NoConn
Set myDB = CurrentDb()
strConnect = "ODBC;UID=kschaefer;PWD=******;DSN=DSN of database you want to connect to;Database=DataMart"
Set myDB = OpenDatabase("", False, False, strConnect)
myDB.Close
Exit Function
NoConn:
MsgBox "You cannot connect to MySql, contact your system administrator !"
End Function
0
Leigh PurvisDatabase DeveloperCommented:
So is that what you were after all along?
I have to admit - I couldn't tell beyond after a while. :-)

That's a DAO database object you've returned.
But after moving from a passthrough query - to an ADO connection - to table linking, I wasn't sure where to go with it.

Glad you got sorted though.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.