How well can MS Access play with an Oracle DB (ODBC confusion)?

I am tasked here at work with coming up with a way for folks using MS Access to create (or at least write to) tables in an Oracle database. The acronym ODBC (Open Database Connectivity) has been thrown around, but no one here really knows how it works.

I have all the Oracle privileges I need … just need to get a basic understanding of what ODBC can do. Can true-blue DDL be passed to a Oracle DB from inside of Access? The idea is to take tables that exist in an Access DB and have them built in Oracle. I know several “long” ways to do this, but we are trying to do it in the most efficient method possible.

Another hang-up: I have little knowledge of Visual Basic (though I can sort out what a VB script is doing, most of the time).

quick off-the-cuff reply:

Oracle can, I believe, accept DDL from ODBC. However, I don’t know of any way that msAccess can output DDL over ODBC except by using a VBA (access-VB) routine. IIRC built-in, non-scripted support for ODBC in access is pretty much confined to importing or linking data, not exporting or anything similar.
I’m familiar with ODBC largely as a programmer’s tool. Once you have a basic understanding of VB or some other language that can use it, ODBC becomes enormously powerful. Until then, you’re dependent on ODBC features that have already been implemented for you.

Hope this helps.

Will you be responsable for the Oracle database? If so then you might want to limit how much Oracle table creation can be done by the Access users. It is easy to write and read to Oracle tables from Access once you have an ODBC setup simply by using the Get External Data, Link Table stuff in Access.; Granting DLL access to Access strikes me as dangerous and to be avoided if possible.

I will be doing the table creation. I am not responsible for the Oracle database.

Later on down the line, not-so-techie Access users will be inserting data into these Oracle tables I’ve built – but they won’t be able to CREATE/TRUNCATE anything in Oracle.

Thanks, guys.

Software engineer here, who’s worked more with ODBC and its various incarnations than I care to recall.

Short answer: You can’t do it.

Longer answer: ODBC is a programmatic interface; it’s not designed for the end user to use “raw” and you’re going to have a big long learning curve if you only have a basic knowledge of VB. Access and MS SQL Server play together a little better than Oracle and Access, but either way I don’t think it would be especially straight forward or fast for you to do what you’re proposing.

If you really need these users to get access to Oracle, I’d look into something like Toad (an Oracle front end) for them to use. It’s bloody expensive, though. Maybe someone else can suggest something cheaper.

Ugh, I did this once - but having trouble remembering how it went. It is most unlovely to try and create Oracle tables through Access - because Access sucks. Seriously, we had a very pernicious bug with an on-site application that I finally tracked down to the fact that, despite the design saying different, one field had been imported to Oracle from Access as a string type rather than a proper number type.

Sorry I can’t be of much more help, but I may remember more later on…

So I can just build the tables in Oracle the conventional way, and use stock ODBC from Access to populate the newly-built Oracle tables?

You know how I think we did it? Exported the Access database structures to a generic text-based format… CSV, maybe - and used an Oracle import tool to bring that in. I don’t think the table-building went through ODBC at all.

The person who is hawking the ODBC strategy talks fondly of a past project where Access and SQL Server worked well in tandem.

Interesting. I wonder if it was through ODBC, or Microsoft Custom hooks.

Yes. This is the only way I have used Access and Oracle together, and most likely the most common way.

Just create the tables in Oracle first as you would ordinarily and then create a linked table in Access. You can then do everything you want to the data in the linked table in Access and it will automatically apply the changes to the underlying Oracle table.

I use MS SQL these days, but the short answer here is yes.

You can create the tables in Oracle.

Set up a user login for he SQL server with the proper select/insert/update permissions. No delete permission, if they need to delete something do it using a flag so any mistakes are recoverable.

Build the structures for the Oracle tables soundly, with the proper indexes and data types.

Link the tables via ODBC in MS Access. From here I’d recommend creating Access Forms to manage to addition of data, though you could do it from teh basic spreadsheet view. Using a form however allows you to validate info and control what the non-tech users can do.

Probably wishful thinking … but can Access export it’s table structures to a text file? If so, it should reduce the time needed to code all of the Oracle table creation.

You could create a table with a negligable amount of data, export it to a txt file. then do a import into Oracle. That will create the table with column headers, but you’ll still need to spend time adjusting data types/lengths and creating control structures.

Creating tables in Oracle is dirt simple anyways, every bit as easy as Access IMHO.

There is a freeware VB program out there to convert an Access db to MySql sql scripts for popultaing a MySql database. I have modified this to write Oracle scripts and use it regualrly for transfering data from a legacy Access database system into Oracle. Email me for a copy of this VB script if you like., it is a little hacky if you want to use Oracle multiple tablespaces as I did, and there is a bug in dropping Indexes that I haven’t yet bothered to fix (it doesn’t do anything bad, just causes error messages when you run the sql scipts generated on Oracle.

Access can connect quite nicely to Oracle database, there are a few issues with datatype incompatability but these are avoidable.

Right … just trying to save some typing. I will have to eyeball the Access tables (and there are over 200 of them … hence the desire to automate this stuff somehow), mentally convert the Access structures into their Oracle counterparts, and type out the table creation code into proper SQL scripts.

Note to all:

Wow. Things may have changed with Access recently … but I was just now able to Export an Access table in such a way that an exact copy of that table was built in the Oracle DB.

Caveat – all text fields are VARCHAR2(255). We can live with that for the nonce, fortunately.

Here are the steps from Access Help:

Off topic, but if you’ve got over 200 tables in your database, I’m of the opinion that you need either a bit of normalization or a serious housecleaning.

I can’t imagine an application which require that many disparte tables unless they are using it for things that Excel should be doing.

It depends… An enterprise system with >200 tables is perfectly normal. One guy’s database with >200 tables is quite unusual. In either case, a bit of normalization in a denormalized database would likely increase the number of tables instead of decreasing it.

Of course, that’s assuming we aren’t talking about such funny business as creating a new table for each department with the exact same structure (and there is a time and a place for that as well).

That said, it’s probably the housecleaning that will do the trick ;).

… and Toad is less dangerous than allowing them to do DDL through Access?!?

I guess it all comes down to what kind of permissions the database user has that you allow your “local” users to work through.

While we all have Toad licenses here, I have a fondness for Golden Retriever by Benthic Software. It’s a lot less flash than Toad, but at a measly $25.00 for a one-user license, it’s the price/performance leader.