Scirpt response for ODBC user id and password

[RonMcNatt]

I am using Microsoft Access 2002 as a front end to conenct to our Oracle Databse through ODBC drivers.

When I run a query, I am prompted for the user id and password to access the Oracle Database. Is there any way I can script a response for this info, maybe through a module? I have an example of some VBA code that does this, but it's not very clear how it is set up.

I have created an automatic task to run the query in the early morning hours, but no one will be there to enter in the username and password.

Thanks in advance for your hellp.

Ron

 

[Guest_imported]

Hi Ron

I use a SQL Pass-Through Query to connect ot our Oracle database.
Do the following:
1 Open a New Query in Design View
2 Make sure the Properties box is open.
3 Close the Show Table box
4 Click Query | SQL Specific | Pass-Through
5 Write a simple query in SQL such as 'select * from [a table in the Oracle database];'
6 In the Properties box set 'Returns Records' to No
7 In the ODBC Connect Str field click the Build button and navigate to your Oracle database.
8 Your usual logon dialog box will open up. Complete it as you would normally and Enter.
9 You will be asked if you want to save the Connection String. Click Yes and the details will be entered into the Properties box.
10 Save the query with an appropriate name ('Logon'?)
Now, when you run this query you will be logged onto your database quite invisibly.
You could run this query before your report query in your timed application, and get a connection with no human intervention.
It works for me anyway! Good luck!

 

[Guest_imported]

Hi Ron

I use a SQL Pass-Through Query to connect ot our Oracle database.
Do the following:
1 Open a New Query in Design View
2 Make sure the Properties box is open.
3 Close the Show Table box
4 Click Query | SQL Specific | Pass-Through
5 Write a simple query in SQL such as 'select * from [a table in the Oracle database];'
6 In the Properties box set 'Returns Records' to No
7 In the ODBC Connect Str field click the Build button and navigate to your Oracle database.
8 Your usual logon dialog box will open up. Complete it as you would normally and Enter.
9 You will be asked if you want to save the Connection String. Click Yes and the details will be entered into the Properties box.
10 Save the query with an appropriate name ('Logon'?)
Now, when you run this query you will be logged onto your database quite invisibly.
You could run this query before your report query in your timed application, and get a connection with no human intervention.
It works for me anyway! Good luck!

 

[RonMcNatt]

Thanks for the suggestion.

Ron

 

[melakeside]

We use this is Access97:

First: Module

Option Compare Database
Option Explicit

'In order for the program to run successfully, the ODBC System Data source
'should appear as: Amisys-Live
'
'The error code associated with this problem is 3151. To fix this problem:
'
'Start->Settings->ControlPanel->ODBC Data Sources->System DSN
'If there are any spaces in the data source, click the configure button
'and delete the spaces
'
'



Function RelinkOne()
On Error GoTo Errmsg:
'Relinks one of the Amisys Attached tables
'which will make Access recognize the current machine's user
Dim db As Database, tdf As TableDef
Dim rst As Recordset
Dim mytype As Integer
Dim myname As String
Dim mynewname As String
Dim foreignname As String
Dim sql As String
Dim mymess As String
'Dim sngStart As Single
'Dim sngEnd As Single
'Dim seconds As Single

'sngStart = Timer



Set db = CurrentDb
Set rst = db.OpenRecordset("msysObjects&quot

'Loop through System Objects table until finding the first attached table
DoCmd.Hourglass True
rst.MoveFirst
Do Until rst.EOF
mytype = rst![Type]
If mytype = 4 Then 'Relink just one table then end program
myname = rst![Name]
mynewname = myname & "a"
foreignname = rst![foreignname]
Set tdf = db.TableDefs(myname)
tdf.Name = mynewname
Set tdf = db.CreateTableDef(myname)
tdf.Connect = "ODBC;DSN=AMISYS-LIVE;DATABASE=;;"
tdf.SourceTableName = foreignname
db.TableDefs.Append tdf
sql = "Drop table " & mynewname & ";"
db.Execute sql
DoCmd.Hourglass False
'sngEnd = Timer
'seconds = sngEnd - sngStart
'MsgBox "This program took " & seconds & " seconds"
Exit Function
End If
rst.MoveNext
Loop



DoCmd.Hourglass False
Set db = Nothing


Errmsg:
Set tdf = db.TableDefs(mynewname)
tdf.Name = myname
mymess = "Error code " & Err & "." & Chr(10) & Chr(13) & Chr(10) & Chr(13)
mymess = mymess & "There has been an error in relinking the attached Amisys tables. "
mymess = mymess & "Please call the IM department for assistance."
MsgBox mymess, vbOKOnly, "Important!"
DoCmd.Hourglass False
Exit Function


End Function

Second: Autoexec Macro
Run Code =relinkone()