SyBase to Access port needed

Question

I have a SyBase database and have been tasked with exporting it to MS Access. What is the best way to do this?

(Note: This is half an "I'll answer it my self when I figure it out" question and half a "If someone known off hand..." question, so don't go doing research unless that's your idea of fun)

Answer 1

Think of it not as exporting from SyBase, but importing into Access (Jet). Assuming you've got an ODBC driver for SyBase that Access/Jet can use, it's pretty simple:

1. In Control Panel | Admin Tools | Data Sources, set up a DSN that points to your SyBase database.

2. Within Access, create a new, blank MDB.

3. From the FILE menu, choose GET EXTERNAL DATA.

4. Choose IMPORT.

5. In the FILES OF TYPE dropdown at the bottom of the dialog, choose ODBC Databases.

6. Choose the DSN.

7. The list of tables in your SyBase database should pop up. Select the ones you want to import and click OK.

The tables should be imported if everything goes as expected. It will include both table structure and all the data. It won't include things like triggers (which Jet doesn't support), referential integrity rules, stored procedures and so forth. I don't know if you can import views -- Access/Jet may treat a view as a table instead of as a SQL because that's certainly the way it treats them via ODBC (you can link to a view just like you can link to a table). You might be able to cut and paste the SQL of your SyBase views directly into an Access save query, but Access/Jet has its own SQL dialect.

You might have an easier time of it if you set your new database to use what the Access UI calls "SQL Server Compatible Syntax (ANSI 92)." Given that SQL Server is a fork from older versions of SyBase, this might make the SQL a bit more compatible. To set this, in Access, go to TOOLS | OPTIONS | ADVANCED and look in the bottom right corner, where the choices should be self-explanatory. This does not give you fully compatible SQL, but it uses %/_ wildcards and allows () for derived tables (instead of the horrendous []. As Alias syntax of Jet SQL). It may also have an effect on some join syntaxes, but I'm not certain about that.

If you have problems, post back and we'll try to help. I don't have SyBase to test with and just used my local MySQL installation to import via ODBC, so it could be that things won't work exactly the same for you.