|
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. |
|
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):
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. 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.
(26 Feb '13, 20:03)
Siger Matt
Replies hidden
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).
(27 Feb '13, 03:58)
Volker Barth
|
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:
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
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.
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.
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?
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.