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.