ODBC connection works once

[52]

I have an Access db that uses Oracle linked tables. Very strange, the connection works once and when I close the app and open it, I can't connect to the Oracle table anymore. (this also happens in a brand new Access database with only an Oracle connection). I've tried deleting the DSN and making a new one - and nothing happens (the connection only works once). I have an application running over the network for almost a year with the Oracle-linked tables and just now am experiencing this. (Also, there are only 2 of us in the group that this happens to - the connection works for the rest of the group).

Any ideas? -- thanks

 

[PCLewis]

Have you checked whether it's your PC's or your user ID's? You said it only happens to two people but are you sure it's the people?

 

[52]

I'm really not sure what it is - I just moved up to Access 2003 (from 2002). Also found this article from Microsoft, almost seems hopeless:

http://support.microsoft.com/default.aspx?scid=kb;en-us;325835#kb3

from microsoft: "Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article" - it applies to 2000, but I just upgraded.

 

[cmmrfrds]

The problem appears to be with the Linked Table Manager.

Don't use the Linked Table Manager, instead link your tables through vba code as a DSN less connection.

I do this with sql server. Here is an example that maybe you can modify. The 1st function links 1 table, and 2nd function relinks the table if necessary.


Public Function SQLServerLinkedTable()
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={Boxer};" & _
"Database=ReportInventory;" & _
"Trusted_Connection=No;" & _
"Uid=yourlogin;" & _
"Pwd=yourpassword;"

' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = CurrentProject.Connection

' Create a new Table object
Set oTable = New ADOX.Table

With oTable
.Name = "dbo_tblReportRequestPrelim"
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = "dbo.tblReportRequestPrelim"
.Properties("Jet OLEDB:Cache Link Name/Password") = True
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With

' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Set oCat = Nothing

End Function

Public Function SQLServerLinkedTableRefresh()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table

Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={Boxer};" & _
"Database=ReportInventory;" & _
"Trusted_Connection=No;" & _
"Uid=yourlogin;" & _
"Pwd=yourpassword;"

' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If tbl.Type = "LINK" And tbl.Name = "tblReportRequestPrelim" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString
End If
Next
End Function

 

[PCLewis]

Just referring to your last reply '52'. If yuo are not sure whether it is a PC issue or a USER ID issue, what hope do you have to find the right answer?

Do the same user id's logging in on known good systems work?
Do known good user id's logging in on your PC work?

 

[52]

No, it's definately not a "user id" error. There is only one user id for the oracle connection. As for the PC's, I doubt it, all PC's are configured exactly the same.

Going to have to look at the VBA that cmmrfrds posted (That was a helpful post).

Very strange - the ODBC connection works with other apps, and as I said, the connection works once - if it were a password issue, I wouldn't be allowed to create it the one time.

 

[cmmrfrds]

Good source for information on various connection strings.

http://www.carlprothman.net/Default.aspx?tabid=81

 

[gmp391977]

Did yout remember to check the 'save password' when you selected the table in question? ODBC will drop its connection if it does not 'remember' the password.

 

[52]

That did it (save password) - what a weird thing. Funny that it's sitting on the network and works for some people and not others. (Now it's VERY slow, but better slow than not work at all)

Thanks for all the help!!!