• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

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
0
Karen Schaefer
Asked:
Karen Schaefer
  • 11
  • 8
1 Solution
 
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 SchaeferAuthor 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Karen SchaeferAuthor 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
 
Karen SchaeferAuthor 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 SchaeferAuthor 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 SchaeferAuthor 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 SchaeferAuthor 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 SchaeferAuthor 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 SchaeferAuthor 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 SchaeferAuthor Commented:
I am still looking for a good solution.

Thanks,

Karen
0
 
Karen SchaeferAuthor 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 SchaeferAuthor 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now