I loaded msaccess with some bespoke tables so that I could copy this to oracle, but how…
Loading is simple, just import data, choose ODBC. Make sure that you create a system naming convention ODBC data source so that access can see the list of tables.
Use SQLDeveloper 3+! Do not use the older versions, they do not have the options that you need for granular table selection.
Get it from here
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
Make sure that you have jdk 1_06.35, or it ain’t going to work…
Expand the zip contents and run the sqldeveloper.exe bin in the \sqldeveloper\bin dir (der!)
Try a connection from SQLDeveloper to your access database
If you get “No read Access to system Tables. Modify Access db before retrying” – you’ll need to change the DB permissions and options.
Now alter the permissions for your database.
I did this in 2000
First, open the DB and goto tools-options and ensure system objects is checked
Then goto tools->security->user and group permissions
Choose users and users radio button and then give (all) appropriate users “read data” and “read design”. Do this for all Msys* tbales.
See above
Do that for all 5 system tables and then do the same for the groups (this is what I did).
Exit the DB
Then do the connection again via SQL Developer
Terrific!
You see your tables and access databases listed.
You select your tables in access and right click and choose copy.
Make sure you have an oracle connection defined as the owner of the schema that you want the data in (in my case, PRODDTA).
1 comment:
My organization get periodic data deliveries from a government agency in the form of access databases. When I demoed this functionality to the people that load the data earlier this year, I blew their minds with how easy it was. Plus, since the mdb is always in the exact same format and file name, they just have to drop it in a pre-defined place and SQL Developer already knows all about it.
Post a Comment