ADO connection to Oracle database

[Kinger43]

Here's the code:

Dim srcDB As ADODB.Connection
Dim srcRS As ADODB.Recordset
Dim txtStatus As String

Public Sub OMS_Update()
Set srcDB = New ADODB.Connection

srcDB.Open "database", "username", "password"
srcDB.CursorLocation = adUseServer

After this it just goes into calling a bunch of queries from the Oracle database to do a bunch of updates on linked tables.

The problem that I am having is in this line, "srcDB.Open "database", "username", "password"." This opens a dialog box with the database and username fields filled in but leaves out the password for whatever reason. Any insight as to why?

 

[SOS]

You actually should probably use the connection string listed in this list (depending on your situation).

 

[Kinger43]

It isn't my code, I can't touch anything on the Oracle server, but it is my job to make sure this code runs everyday. I can't go changing connection strings etc. Do you have any insight as to why this one isn't working? I had gotten this problem resolved several months ago and the problem has reoccured for seemingly a different reason as the original fix isn't working.

 

[SOS]

It isn't my code, I can't touch anything on the Oracle server, but it is my job to make sure this code runs everyday. I can't go changing connection strings etc. Do you have any insight as to why this one isn't working? I had gotten this problem resolved several months ago and the problem has reoccured for seemingly a different reason as the original fix isn't working.

Umm, yes you need to change the connection string. It isn't that you are doing this on the server or anything it is for YOUR connection that is in your code.

So, instead of using

Set srcDB = New ADODB.Connection

srcDB.Open "database", "username", "password"

You use

Set srcDB = New ADODB.Connection

srcDB.ConnectionString = 'The applicable string from ConnectionStrings.com here'

 

[Kinger43]

Again, not my code. I'm not going to go changing someone elses code.

 

[SOS]

Again, not my code. I'm not going to go changing someone elses code.

Just comment it out so it stays there, but you are going to have to if you want it to work. Otherwise, feel free to keep spinning the wheels.

 

[Kinger43]

Why did it work a month ago and stop working now if this is just spinning my wheels? You are going to have to elaborate on why I have to change the code.

 

[SOS]

Wait a minute - are these tables LINKED already?

 

[Kinger43]

Yes, all the appropriate tables are linked to the Access database already.

 

[SOS]

Then you just need to use this for the connection:

Set srcDB = CurrentProject.Connection

and that is actually the PREFERRED method so it elminates any problems with Access already linked to it and then you trying to link using another connection which isn't necessary.

So, yes the code should be changed but it will actually be a good change and one which will help keep extra connections (which aren't needed) from being opened.

 

[Kinger43]

Code now looks like this:

Public Sub OMS_Update()
   
Set srcDB = CurrentProject.Connection

'srcDB.Open "database", "username", "passord"    
srcDB.CursorLocation = adUseServer

DoCmd.SetWarnings False

Set srcRS = New ADODB.Recordset
[COLOR="red"]srcRS.Open "TRUNCATE TABLE OI_LOC", srcDB, adOpenDynamic, adLockOptimistic, adCmdText[/COLOR]txtStatus = "Dropped OI_LOC"
Forms!F_START_UP!txtStatus.Text = txtStatus

When I run it it gives me an error that it expected SQL statement DELETE, SELECT, etc. When I click debug it goes to the highlighted line above.

 

[PC User]

Do you download data into a database table through your connection? How do you insert the data into the table after its downloaded?

 

[Kinger43]

Do you download data into a database table through your connection? How do you insert the data into the table after its downloaded?

I don't follow.

This code takes the linked Oracle tables which I cannot access and runs update and append queries to update and append the Oracle tables with Access tables that I do have access to. Changes are made daily to the Access tables and then those changes are applied to the Oracle database in the evening via this code.

 

[SOS]

You would still use the same CurrentProject.Connection. Have you tried it? You should just comment out the other code by adding an apostrophe to the beginning of each line of the existing code ' and then put in the part I said and see if it works.

 

[Kinger43]

I know how to comment things. I'm not completely ignorant.

So you are saying I should have this as well?
set srcRS = CurrentProject.Connection

 

[SOS]

I know how to comment things. I'm not completely ignorant.

So you are saying I should have this as well?
set srcRS = CurrentProject.Connection

No, srcRS should be

Set srcRS = New ADODB.Recordset

srcRS.Open "TableOrQueryNameHere", srcDB, adOpenDynamic, adLockOptimistic

 

[Kinger43]

Code now looks like this:

Public Sub OMS_Update()
   
Set srcDB = CurrentProject.Connection

'srcDB.Open "database", "username", "passord"    
srcDB.CursorLocation = adUseServer

DoCmd.SetWarnings False

Set srcRS = New ADODB.Recordset
[COLOR="red"]srcRS.Open "TRUNCATE TABLE OI_LOC", srcDB, adOpenDynamic, adLockOptimistic, adCmdText[/COLOR]txtStatus = "Dropped OI_LOC"
Forms!F_START_UP!txtStatus.Text = txtStatus

When I run it it gives me an error that it expected SQL statement DELETE, SELECT, etc. When I click debug it goes to the highlighted line above.

And here's what happened.

 

[SOS]

Are you trying to run a stored Procedure?

 

[Kinger43]

No, I assume it's getting hung up on the TRUNCATE part.

I really don't think I need to change connection strings etc. The code works fine except for the srcDB.Open line. And as far as that goes it works except for putting in the password. This code up until a month or so ago worked just fine. No problems, no hitches. There haven't been any version changes to any software involved, no hardware has changed. Nearest I can tell, I came in one morning and it decided it wasn't going to work anymore.