Can SQL scripts be passed to an Oracle database from within a Visual Basic script?

I am trying to write up a workaround that involves passing SQL statments to an Oracle database from within a VB script (running from MS Access 2003).

I know nothing of Visual Basic. Can it do what I am needing here?

I opened up some Access forms other’s have created, to view the underlying VB and try to “backform” embedded SQL within VB.

Here’s an example of some VB code that I found. I hope it’s not completely meaningless without context:

 ' Open the table of Switchboard Items, and find
    ' the first item for this Switchboard Page.
    Set con = Application.CurrentProject.Connection
    stSql = "SELECT * FROM [Switchboard Items]"
    stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
    stSql = stSql & " ORDER BY [ItemNumber];"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open stSql, con, 1   ' 1 = adOpenKeyset

The inclusion of this SQL code in the VB is a little confusing. There is a reference to “Application.CurrentProject.Connection”, but it is not defined anywhere in the VB script. Maybe an earlier VB script ran ahead in sequence that declared this variable?

Well, anyway, if anyone can lend some expertise, I’d be much obliged.

If anyone is wondering what all this is a workaround for, it is for the following – we’ve got an Update Query in Access that atempts to join a regular Access table to an Oracle-linked table, then derive a figure from the Access table and update all the rows in the Oracle table based on that derived figure. Well, Access doesn’t seem to like UPDATE or DELETE statements being run against Oracle-linked tables.

I am satisfied that it is not a question of privileges. This has been checked thoroughly, and all necessary privileges are in place.

Okay, a few basic points.

1- that code is not entirely meaningless to me… it’s a standard query notation to fetch information for a switchboard… that is, an MSaccess application menu whose various options are kept in a database table. (Always seemed like a slightly odd notion to me, but hey.)

2- Nobody else needs to set Application.CurrentProject.Connection Application.CurrentProject is a built-in class in access visual basic, containing a bunch of useful references to other things that are a part of your MSaccess database. Application.CurrentProject.Connection , for instance, is “give me the connection to this database file” There is a connection that is already being used behind the scenes for access to automatically run queries and display table results, etcetera, so you can take that connection and run queries on it instead of going to the trouble of opening your own.

If there is anything else you’re trying to ask, I’m not sure what it is. Perhaps you can elaborate more, given those answers??

Thanks, chrisk. I think I messed up the OP, though.

I think the sample script I gave is only passing SQL to Access tables. Whoops – never mind.

Here’s what I’m trying to do:

I’ve got an Access form here that needs to be modified. Part of this form used to kick off an Access update query that modified data within an Access table. Simple enough.

But now I need this form to modify data in a remote Oracle database table. A version of that Oracle table is linked to the Acess db, and it appears right alongside all the regular Access tables (with a globe icon, though).

Now, I can sucessfully INSERT data into that Oracle-linked table through the form … but I can’t run either an UPDATE or a DELETE against that table from within the form. Attempting either results in an “operation must use an updateable query” error.

I was hoping that I could employ a workaround that made the Oracle database do the necessary data modification on its end, instead of on the Access end. Therefore, I am looking for a way to pass working SQL to an Oracle database through Visual Basic (so that I can modify the form).

Okay, sorry I didn’t get that.

What you’d need to do then, probably, is create a new connection. (You could use the access connection since the table is ‘linked’ in, but this does have its problems.) So, instead of using application.currentproject.connection, you’d say something like this to start off the script:

set con = new Connection “Provider=msdaora;Data Source=orasrv01;” & _
“User Id=scott;Password=tiger”

Where you fill in the values for data source (the oracle server name) and the userID and password to connect yourself. Then continue as in the rest of the example.

Hope that helps.

Thanks, chrisk.

I the above suggestions don’t work, make sure you have the proper native Oracle permissions for whatever username you’re connecting as.

He’s doing this in script. It’s a little different than VB. It would be more like this:

set conn = CreateObject(“ADODB.CONNECTION”)

I’m not at all certain that the OP meant ‘script’ in that context… (he doesn’t seem to have a very firm grasp of the programming terminology. :slight_smile: )

I suspect that what he meant by a ‘script’ was actually a block or routine of VBA code in his access DB. That isn’t technically vbscript, and the " = new connection" syntax will work just fine.

But so will your syntax. shrugs