Solved: Connect to oracle with vba (access)

[shiva.h]

Hello world ;)

I'm presently trying to realise a program that connect to an oracle database and import tables into access using VBA. I have searched and can't find a way to make it work. I think the connection to the database is okay but I'm not even sure, is there a way to be sure ? How do I execute sql queries in VBA ?


Thanks to anybody who can help me.


G.

[lucas]

moved to access help forum

[shiva.h]

moved to access help forum Thanks, I was desperatly trying to move it in the right place.

[lucas]

You'll get content appopriate help here.

I'm not any help with Access but many here will come along soon and give you some pointers.

Welcome to the board.

[CreganTur]

You can connect using ADO. If you're not familair with it, then checkGoogle- it's a slightly complex process and there are a ton of great resources out there.

You can find the connection string you'll need using www.connectionstrings.com.

[shiva.h]

I know I must use ADO, I said I think the connection is working which means I already figured that out but when I try to use queries to get some informations from my tables I get an error message ( such as you're in a synchronous connection, you can't do that)

[Imdabaum]

Why not use linked tables and skip the ADO stuff? Granted ADO provides a few more features, but I thought I'd throw that option out there for you. It's relatively simple to link the tables once you have the rights and the Oracle DNS registered in your datasources.

That's how I used most of my oracle tables in Access as generally Oracle tables are there because you have some larger scale application using it; website, intranet, interoffice desktop applications etc.

[shiva.h]

that's what I ended up doing because I never found anything that worked. But I'm not really satisfied because the "application" I'm doing with access will be used by basic users and I don't really like the fact that it is directly linked to my oracle database. Anyway, I didn't find anything else so I guess I'll have to accept that ;)

thanks for your answer !

[Imdabaum]

There's always a little more overhead to manage, but it's possible to create a view in Oracle and link Access to the views. That way you have the data you need for Access but don't have to worry about the Master data in Oracle getting modified.

[shiva.h]

I don't really know how to do that but I could search this way. For the moment I'll stick with that solution as they need it very soon but I'll try to propose this option to my boss. For the moment I only use select queries it should not be too dangerous ( also I don't really have access to the database, I'm not on charge with it and we don't have a lot of tools to work on it :/ )

[Imdabaum]

I wasn't in charge of the Oracle at my last job either, I had to get a DBA to build it. It didn't seem to take to long, but sounds like you've got it working for now. Let us know if you need any other advice or solutions. Good luck.

[shiva.h]

yep it seems to be working but I'll propose your solution and see if they're interested. Thanks a lot !