Passthrough Code in Access

[Timo van Esch]

Hi,

It's been a while since I needed this forum. I learned a great deal browsing around, gave some comments and continued developing for the company I work for.

Now I've stumbled upon a whole new problem: All morning I've been looking to create a simple pass-through query in Access.
Problem: through ConnectionString I am able to TRUNCATE a table, but I don't get the INSERT command to work.

I have this to wrap my SQL statement in:

Function Test_SQL_PassThrough(ByVal ConnectionString As String, _
                              ByVal SQL As String, _
                              Optional ByVal QueryName As String)
On Error GoTo Err_Handler

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef
    With qdf
        .Name = QueryName
        .Connect = ConnectionString
        .SQL = SQL
        .ReturnsRecords = (Len(QueryName) > 0)
        If .ReturnsRecords = False Then
            .Execute
        Else
            If Not IsNull(dbs.QueryDefs(QueryName).Name) Then dbs.QueryDefs.Delete QueryName
            dbs.QueryDefs.Append qdf
        End If
        .Close
    End With
    Set qdf = Nothing
    Set dbs = Nothing

Exit_Handler:
    Exit Function
Err_Handler:
    MsgBox Errors.Count & Err.Source & Err.HelpContext & " Error " & Err.Number & vbCrLf & Err.Description & vbCrLf, , "ERROR"
    Resume Exit_Handler
End Function

This is my first command:

Test_SQL_PassThrough(ConnString, "TRUNCATE tbl_name;")

This one works, no problem. That means the connection string is ok; I make a connection with the MySQL server and delete all contents.

Then I use the same function to insert data, and that fails with a 3146 error "ODBC--Call failed."
All the SQL statements below fail with the same error:

INSERT INTO tbl_name_mysql ( COLUMN_30 ) SELECT tbl_name_access.COLUMN_30 FROM tbl_name_access LIMIT 5;
INSERT INTO tbl_name_mysql ( COLUMN_30 ) SELECT COLUMN_30 FROM tbl_name_access LIMIT 5;
INSERT INTO tbl_name_mysql ( COLUMN_30 ) SELECT COLUMN_30 FROM tbl_name_access;
INSERT INTO tbl_name_mysql ( COLUMN_30 ) SELECT COLUMN_30 FROM tbl_name_access LIMIT 5;
INSERT INTO tbl_rpm_FERT ( COLUMN_30 ) SELECT ( COLUMN_30 ) FROM tbl_RPM_FERT2009;

COLUMN_30 is the primary key in MySQL, that's why I try to insert this one. When I run it in ACCESS as an append query I have no problems. But when I try to run it through either VBA or through query builder as a passthrough query, it fails.

When I do the same with a usual append query and a linked ODBC MySQL table, it has no problems. Only when I try to make it work on server-side, it fails.

Has anyone experience with passthrough queries?
And would you be so kind to help me out with this one?

With kind regards,
Timo

 

[DavidAtWork]

Running a simple SQL statement such as TRUNCATE table will work because you are dealing with only one database object on the SQL server side, you set your connection and execute, no problem.
The problem comes when you try to execute a sql statement that takes records from access to sql server, I don't believe you can do this in a single action unless the sql server table is already linked (via ODBC) to the access database.
The only way I see it could be done is by using the open recordset method (your source from access), take it record by record and use stSQL = "INSERT INTO sql_table(field1, field2) VALUES('" & recordset.fields(1) & "', '" & recordset.fields(2) & "')"
Then open your connection to sql server and execute using
.Execute (stSQL)
This would have to be done record by record until recordset.EOF

David

 

[namliam]

David is correct unless you actually link the table in access you cannot use an insert statement inside a pass-thruough query sourcing a table in access.
I believe creating the table link and running the insert over that will be easier and faster than looping seperate insert statements.

On a side note, why would you want to upload only your PK to the server?

 

[Timo van Esch]

Hi guys,

Thanks for taking the time!
This morning I -indeed- found out myself that running server-side code does not work. Of course MySQL does not recognise the Access table, once I send the SQL statement to the server.

I've run a good test with ODBC connector 5.3.2. I really wanted to see if passthrough would speed things up, but as I see it now, it is not possible. Simply because MySQL does not recognise the Access table(s), pulling the data into MySQL is only possible with migration tools. And that's not what I'm looking for.

The reason I want to plant this data in MySQL is, because we run about 3GB of data per year per table. And we have around 6 of these gigantic tables. So we are looking for a solution to store them into 1 MySQL server, instead of 12*6 monthly databases (250MB each).

Thanks for your help!
Timo

ps. method I used (and works), with yeartables as a testcase (not the actual data, but 6,63GB in total):

    For i = 1 To 4
        rTable = "tbl_rpm_" & sCat(i)
        sSQL = "TRUNCATE " & rTable & ";"
        CurrentDb.Execute sSQL, dbSQLPassThrough
        Call Test_SQL_PassThrough(strConnect, sSQL)
        For j = 2009 To 2013
            sTable = "tbl_RPM_" & sCat(i) & j
            sSQL = "INSERT INTO " & rTable & " ( COLUMN_30 ) SELECT ( COLUMN_30 ) FROM " & sTable & ";"
            CurrentDb.Execute sSQL
        Next j
    Next i

 

[namliam]

If you setup your table on mySQL side, create the linked table you should be able to use an append query (inside access) to get your data from your access table to mySQL.

This should be fair side faster then doing individual insert statements.

 

[Timo van Esch]

Hi namliam,

That's what I did indeed.

But in running tests with a large dataset, I found out that pushing 6.63GB to my local MySQL install is almost 3x faster than to my network MySQL install. That's why I wanted to find out if passthrough goes any faster.

Until I found out it is difficult, if not impossible, to make MySQL understand where to get the data. Once the SQL is passed to MySQL, there is no way of knowing (for MySQL) what database has the original tables. Unless I can find a way to 'handshake' MySQL with my original Access table, by providing a connection string with the information where to pull it's data from...

 

[CJ_London]

I can't quite remember the construction but you can try SQL in Access along the following lines

INSERT INTO mySQLTable ( Fld1, Fld2 ) IN 'Provider=MSDASQL; DRIVER={MySQL ODBC 3.51Driver}; SERVER= localhost; DATABASE=Your_MySQL_Database; UID= Your_Username; PASSWORD=Your_Password; OPTION=3'
SELECT fld1, fld2
FROM myAccessTable

Just found this link

http://office.microsoft.com/en-us/access-help/in-clause-HA001231484.aspx

Which says using a linked table would be better.

my local MySQL install is almost 3x faster than to my network MySQL install. That's why I wanted to find out if passthrough goes any faster.

I doubt it, the relative speed will be down to the level of traffic and the bandwidth - local will be faster than lan which will be faster than wan.

You could try uploading the table to the server as a text file and then run a query from there which should give you similar performance to local

 

[Timo van Esch]

Hi CJ,

Thank you for your help and your link!
It is interesting to see it is possible to send a connection string through SQL; no doubt it will come in handy one of these days. And you feed my suspects: LAN will never be as fast as local, whatever we try. I totally agree.

So, now we are looking for the fastest way possible, taking into consideration the extra deficits of local MySQL servers (f.i. no local Access clients possible, without MySQL instances, which is undesirable)