MS Access Connection String

[former_SQLA_member1694868]

I have an .MDB file that opens in MSAccess that I would like to import into SA 12.

I created a 32 bit ODBC system DSN "Testing" that points to the file using the Microsoft Access Driver (.mdb,.acdb).

I opened the 32 bit Sybase Central and try to connect to it using ODBC and I get an error

Could not connect to the database
Cannot open database '(unknown)'.  
[Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.
SQLCODE=-1206
SQLSTATE=HY000

Connection parameters:
User=
Password=***
DSN=Testing
    ENC=NONE

So I think maybe I was greedy trying to connect directly. Instead, I'll open up a blank database and use the remote server feature to query and import the data.

I used the Create Remote Server Wizard and picked TestSRV as the name, Microsoft Access as the driver and ODBC as the connection type. Then it asks me about the connection information. This is where I get fuzzy. I tried the DSN name and that didn't work, I tried DSN=Testing and that didn't work. I googled MSAccess connection string and came up with this: Driver={Microsoft Access Driver (.mdb, .accdb)};DSN=Testing;

But that complains about Variable 'Microsoft Access Driver (.mdb, .accdb)' not found.

I figure there is something I'm missing in the connection string but I am having trouble finding good examples or syntax of connection strings because it seems to depend on the situation and the direction (into SA or from SA to something else) what syntax to use.

Any and all help would be appreciated.

[MarkCulp]

When you use { something } SA thinks that the "something" is the name of a connection variable and tries to substitute the { something } with the value of the variable.

Try leaving out the { and } characters in your connection string. Example:

Driver=Microsoft Access Driver (.mdb, .accdb);DSN=Testing;

---

BTW: Once you are connected, this page in the docs may be useful: http://dcx.sybase.com/index.html#1201/en/dbusage/access-odbc-omni-server.html

[former_SQLA_member1694868]

Excellent -- Once I figured out that the bitness needed to match across all - 32 bit ODBC, 32 bit engine, 32 bit sybase central (I had been running the 64 bit engine and the 32 bit everything else) everything else worked great.

A feature request if I may - Allow the option to add text to the tablenames to be created. For a migration into our structure I would usually name the source tables srcTableName to keep them together and separate from our real structure. The first time I migrated using the wizard I received an error about there already being a table named State as it existed in both the remote and target. This would be complicated by foreign key definitions and other places that reference the name, but it would be nice to have the option if just moving the data is more important.

[MarkCulp]

Siger: Good suggestion; I can see the benefit to having such an option. Please consider writing a separate question to propose your request and tag it with product-suggestion.

[VolkerBarth]

Hm, the "Create Proxy Table Wizard" does allow to rename the local table (and that's what I did in my sample: The remote table is "T_Test", the proxy table is called "FT_Test"). - Do I have missed the point?

[former_SQLA_member1694868]

Yes it does allow rename when you create the tables one at a time, but if you use the migration wizard it will create every table in one step, but without the option to rename.

[VolkerBarth · Accepted solution]

I have successfully tried to create an Access MDB (with MSACCESS 2003) within one table T_Test and created a SQL Anywhere 12.0.1 database with a remote server connection to the Access MDB. There were no problems doing that.

Here, using the DSN name for the remote server definition does work as expected.

Steps I've used after creating both the Access MDB (located as C:\\ACC_TEST.MDB) and the SA database):

• Create a system ODBC entry for the Access MDB named "ACC_Test", here selecting the database by specifying its file name (and otherwise, leave all defaults).
• Create a remote server in SA 12.0.1 (via Sybase Central) with the according definition:
  

  CREATE SERVER "SVR_ACC" CLASS 'MSACCESSODBC' USING 'ACC_Test';

• Create a proxy table to the desired remote table T_Test table with the following definition:

  CREATE EXISTING TABLE "DBA"."FT_Test" AT 'SVR_ACC;C:\\acc_test;;T_Test';

That straightforward way, I can now simply access the Access table's contents in the SA database:

Basically it seems that although the ODBC DSN contains the MS Database file location, it's still required that this location is explicitly contained in the proxy table definition (however, in my case, that information was added automatically by the Proxy Table Wizard) - I've just copied the Wizard-generated statements here.

---

Note: The MSACCESS mdb I'm using does use the default security concept (i.e. no explicit "users" and "permission" and the like), so there is no need to specify particular credentials for the remote server.

Another note: As Siger, I'm using both a 32-bit SQL Anywhere engine and 32-bit ODBC DSNs. When using a 64-bit engine, one would need to assure the MSACCESS DSN is a 64-bit one, too, as the remote server facility requires an ODBC driver with the appropriate bitness.

[former_SQLA_member1694868 · Accepted solution]

By using this as a "known good" example I was finally able to determine that while I was connecting with 32 bit Sybase Central, the db engine was 64 bit and therefore could not see the 32 bit DSN.
Thanks for the help.

[VolkerBarth · Accepted solution]

Well, the requirement that the database engine and the ODBC driver used for Remote Data Access need to have the same bitness is documented in this Karim answer - just to note that this is different for other "external calls" (aka remote procedures).