How to code copy data from ODBC Sybase to Access?

Question

I mean using code, not from menu button.

For example, Access table name is: ABC_T ; Database(Sybase, it is ODBC data source, not file) table name is ABC

I have code which already connects to database. Now I want to copy data from Sybase database table ABC to Access table ABC_T

The key problem of SELECT * INTO ABC_T FROM ABC is: The program does not understand table ABC, I need to use Cn somewhere in the code to identify that particular database, and I want to code to look into the database for table ABC, not looking into current Access tables for ABC

Thanks.

Set Cn = CreateObject("ADODB.Connection")

DatabaseConnectionString = ".....dabasename/username/password/etc ..."

Cn.Open DatabaseConnectionString  'My code works fine till here. Then how to write remaining codes?

DoCmd.RunSQL "SELECT * INTO ABC_T FROM ABC" 

Answer 1

Well, if you have a linked table, then a simple append query should suffice.

So a simple SQL “append” query would suffice here. You don’t need some “ado” code, or some looping code.

Besides, you can’t append from an ADO connection to a local table because the “ado” does not deal with two separate data sources and connections in ONE ADO object – you can’t do this!

However, you most certainly can with linked tables.

If for some strange reason you don’t have a link to the server side table, then you can create a link on the fly with:

   Dim strSQL As String
   
   DoCmd.TransferDatabase acLink, "ODBC Database", strCon, acTable, "dbo.tblNames", "dbo_tblNames"
   
   strSQL = "INSERT INTO tblNames ( MyName, Age, Info ) " & _
            "SELECT dbo_tblNames.MyName, dbo_tblNames.Age, dbo_tblNames.Info " & _
            "FROM dbo_tblNames"
            
   CurrentDb.Execute strSQL
   

However, it is assumed you likely already have a “active link” to the server database – this means  you can dump the above code that creates the link, and do the append query. In fact, you can even build the append query in the Access query builder and not even have to mess with the sql in your VBA code.

Eg:

Currentdb.Execute “name of saved append query goes here”

So create the linked table (or use the linked table). Then just execute a standard access append query.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada