SQL with 2 connections

[stanl]

This is not a WB issue, per se, but it is written in WB. I began working with a new remote MySQL Server and have to integrate with Access/SQL Server data.  One of the main daily transaction tables in MySQL I linked to Access. That table will eventually hold ~20,000 rows per day. That data is parsed into 2 Access tables which are structured the same as another concurrent application which provides similar data and is parsed by separate scripts.

One of the fields in the linked table is a reference to multiple tables in a different MySQL db. I cannot link to these tables because I do not know in advance what they will be and there can be up to 100 of them a day.

So:  I create an Ace OLEDB connection to the Access db with the linked table (which also holds the tables data is parsed into).  After parsing, I perform a distinct query by date for the field referencing the other tables. The results are placed into a WB list. I then create a dsn connection to the MySQL Server, loop through the WB list to create SQL queries for the specific table, the results used to populate a variable saved as a .csv file. Finally, I re-open the ACE connection and use an ISAM INSERT INTO (with OLEDB Bulk parameter set to avoid duplicates) another local Access Table.

What I would like to do is eliminate the .csv step and just INSERT directly from the MySQL table into the Access table. One option would be to programmatically create and delete 'links' to the MySQL tables... but there is too much chance for error, too much to keep track of, and it is plain ugly.

Be nice if one could manage data between two connections (the ACE and the MySQL DSN). I have looked on Google and Bing finding users with similar questions but no real answers, and most of those were specfic to SQL Server where one could write Stored Procedure. I do not have that luxury with MySQL as I only have read rights to tables.

If you have read this far and are wondering, "why the question, if you have a workaround". Truthfully, the workaround is actually pretty slick and didn't involve that much coding. It is working on a small scale now (1,000 not 20,000 rows), and my concerns are when the data scales up.

   

[JTaylor]

Here is one thing I found, assuming I understand and it applies.

Jim




The fastest method is to use Indexed Sequential Access Method - ISAM.  This allows for directly transfering data from your connected ADO record source to a second dynamically connected datasource.  I figured out how to do this this week and it is the only way to fly!  Do yourself a favor any try this, you will be glad that you did!

1. Use an ADODB.Connection to connect to ANY ADO DB using the standard connection strings (www.connectionstrings.com).
2. Once connected, in the SQL query instead of entering a destination table name use an ISAM name to interact with a seperate DB.
3. Note that the Excel drivers work for BOTH 2003 and 2007 even though the version says 8.0 (it is not 12.0).  This threw me for a bit as 8.0 is how to specify 2003 in a connection string.
4.  Using this method I am able to transfer data between any two data sources (since the initial connection can by to any datasource) using a single SQL statement including CSV, Excel 2003/2007, Access 2003/2007, SQL Server.  I think I have even seen a DB2 example, so others are be possible.

Here is the syntax:

SELECT * INTO <Put ISAM Name Here> FROM <normal query syntax to connected DB and table>
INSERT * INTO <Put ISAM Name Here> FROM <normal query syntax to connected DB and table>
or
SELECT * INTO <normal query syntax to connected DB and table> FROM <Put ISAM Name Here>
INSERT * INTO <normal query syntax to connected DB and table> FROM <Put ISAM Name Here>

...other options are possible, the point is just to replace the table name with the ISAM Name in the query.

ISAM Names:

CSV & TXT
[Text;Database=C:\_Path\;HDR=Yes].[File.csv]
[Text;Database=C:\_Path\;HDR=Yes].[File.txt]


Access 2003 & 2007
[MS Access;Database=C:\Path\File.mdb].[AccessTableName]
[MS Access;Database=C:\Path\File.accdb].[AccessTableName]

Excel 2003 & Excel 2007
[Excel 8.0;HDR=Yes;IMEX=1;MaxScanRows=16;Database=C:\Path\File.xls].[Table$]
[Excel 8.0;HDR=Yes;IMEX=1;MaxScanRows=16;Database=C:\Path\File.xlsx].[Table$]
[Excel 8.0;HDR=Yes;IMEX=1;MaxScanRows=16;Database=C:\Path\File.xlsm].[Table$]
[Excel 8.0;HDR=Yes;IMEX=1;MaxScanRows=16;Database=C:\Path\File.xlsb].[Table$]

SQL Server
[ODBC;Driver=SQL Server;SERVER=<Server Name>;DATABASE=<Database Name>;UID=<UserName>;PWD=<password>;].

Here are some links: http://support.microsoft.com/kb/321686 http://support.microsoft.com/kb/200427 Additionally, if you would like to create a file based CSV, Excel 2003/2007, Access 2003/2007 database via the ADO connection, that can be done using the ADOX object. Set oADOXDatabase = New ADOX.Catalog oADOXDatabase.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & <path to file>) The combination of these concepts allows for the creation and population of a DB file using ADO very quickly. I have working code for this, so I can tell you that it works great if you feel the need...the need for speed! Greg Harward Bob Barker wrote:

[stanl]

Here is one thing I found, assuming I understand and it applies.

Jim; [thanks for looking]

In my initial post I wrote: Finally, I re-open the ACE connection and use an ISAM INSERT INTO (with OLEDB Bulk parameter set to avoid duplicates) another local Access Table.

also, this
[Text;Database=C:\_Path\;HDR=Yes].[File.csv]

should be
[Text;Database=C:\_Path\;HDR=Yes].[File#csv]

My post was about avoiding the ISAM, local file route, in favor of direct SQL between 2 connections. After some more looking I am beginning to think that it is not possible. I have used ISAM for years and it is one of the reasons I am prone to keep Access as middleware when working with Server db's (like Oracle, SQL Server, DB2). I guess in 25 words or less what I am asking is:

conn1 = Ace.OLEDB connection
conn2 = MySQL DSN

INSERT INTO [table in conn1] SELECT * from [table in conn2]

I was thinking of going the ODBC; ISAM route - but on my first attempt I got TimeOut errors.

[JTaylor]

Sorry...must have misunderstood the thread I posted....thought that accomplished what you wanted but avoiding the use of test files and doing a direct Select/Insert operation.

Jim

[stanl]

Sorry...must have misunderstood the thread I posted....thought that accomplished what you wanted but avoiding the use of test files and doing a direct Select/Insert operation.

Jim

1 connection will work, for example:

Code (WINBATCH) Select Expand


oConn = CreateObject("ADODB.Connection")
;also set connectiontimeout and cursor location
cConn = "Provider=Microsoft.ACE.OLEDB.12.0;data source=":[AccessDB]
oConn.Open (cConn)
cSQL = 'INSERT INTO [AccessTbl] SELECT * FROM [MySQLtbl] IN "" [ODBC;DSN=dsn;UID=uid;PWD=pwd;]'
oConn.Execute (cSQL)
oConn = 0


But it has not proved reliable for large INSERTS, or more than 20 iterations. Should probably move to the Command Object where there is more control over timeouts.

Don't know why I got focused on 2 connections...

[JTaylor]

Probably not the most efficient way but I probably would have simply opened a recordset and looped through it and used the results in an Insert.   Didn't realize you could connect the two in that fashion so appreciate the post.

Jim

[stanl]

Didn't realize you could connect the two in that fashion so appreciate the post.

Jim

As it turned out I was over-thinking the problem. When you go the single connection/ISAM route the SQL has to conform to VBA or Access Basic. I'm dealing with MySQL that stores dates as doubles, text in mm/dd/yyyy, text in yyyy-mm-dd but no actual timestamps. When I planned on using two connections I wrote the MySQL using it's distinct SQL functions. Took me several tests, but have the query down to correct VBA syntax and even included a WHERE clause. A simple WB TimeDelay() seems to eliminate issues with up to 100 INSERTS from MySQL tables. Finally, a composite primary key eliminates duplicates. And,  can always fall back on the original script that created the intermediate ISAM .csv file.

[JTaylor]

Sounds like you have it beat into Submission...again, appreciate the post.

Jim