Change ODBC Link Provider String in Access DB using ADOX in VB.NET

[Tim Frawley]

I am attempting to change the ODBC Link Provider String in an Access
database linked to an Oracle server using ADOX in VB.NET.

I created some code using the example from post:
http://groups.google.com/groups?q=Re...4ax.com&rnum=1

My code simply replaces the DSN, UID, PWD and DBQ values in the
ADOXTable.Properties("Jet OLEDB:Link Provider String").Value string
and then I try to set ADOXTable.Properties("Jet OLEDB:Link Provider
String").Value to my new string.

I get the Error:
System.Runtime.InteropServices.COMException (0x80004005):
ODBC--connection to 'C:\DSN\new.dsn' failed.
at ADOX.Property.set_Value(Object pVal)
at test.frmTest.RefreshLinkedTablesWithADOX() in frmTest.vb:line
302

This is the Code:

Sub RefreshLinkedTablesWithADOX()

Dim ADOXTable As New ADOX.Table
Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connection
Dim LinkProviderString As String

Try

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4 .0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;")

ADOXCatalog.ActiveConnection = ADOConnection

For Each ADOXTable In ADOXCatalog.Tables
If ADOXTable.Type = "PASS-THROUGH" Then
LinkProviderString = ADOXTable.Properties("Jet OLEDB:Link " &
_
Provider String").Value
'''''''''
' Manipulate LinkProviderString to replace DSN, UID, PWD and
DBQ
' for new DSN values.
'''''''''
ADOXTable.Properties("Jet OLEDB:Link " & _
"Provider String").Value = LinkProviderString
End If
Next
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
ADOConnection.Close()
End Try

End Sub

I tried to shorten some lines of code so they won't wrap.

There is no security on these access databases as we created them
in-house as a front end query engine for our Oracle database.

Does anyone have any ideas?

[Cor Ligthert]

Tim,

It is ODBC and it is Oracle,

I advise you to ask this in the newsgroup

Adonet
news://msnews.microsoft.com/microsof...amework.adonet

Web interface:

http://communities2.microsoft.com/co...amework.adonet

I give you much more change there for more answers

Cor

"Tim Frawley" <ti*********@fishgame.state.ak.us> schreef in bericht
news:bf*************************@posting.google.co m...

I am attempting to change the ODBC Link Provider String in an Access
database linked to an Oracle server using ADOX in VB.NET.

I created some code using the example from post:
http://groups.google.com/groups?q=Re...4ax.com&rnum=1

My code simply replaces the DSN, UID, PWD and DBQ values in the
ADOXTable.Properties("Jet OLEDB:Link Provider String").Value string
and then I try to set ADOXTable.Properties("Jet OLEDB:Link Provider
String").Value to my new string.

I get the Error:
System.Runtime.InteropServices.COMException (0x80004005):
ODBC--connection to 'C:\DSN\new.dsn' failed.
at ADOX.Property.set_Value(Object pVal)
at test.frmTest.RefreshLinkedTablesWithADOX() in frmTest.vb:line
302

This is the Code:

Sub RefreshLinkedTablesWithADOX()

Dim ADOXTable As New ADOX.Table
Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connection
Dim LinkProviderString As String

Try

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4 .0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;")

ADOXCatalog.ActiveConnection = ADOConnection

For Each ADOXTable In ADOXCatalog.Tables
If ADOXTable.Type = "PASS-THROUGH" Then
LinkProviderString = ADOXTable.Properties("Jet OLEDB:Link " &
_
Provider String").Value
'''''''''
' Manipulate LinkProviderString to replace DSN, UID, PWD and
DBQ
' for new DSN values.
'''''''''
ADOXTable.Properties("Jet OLEDB:Link " & _
"Provider String").Value = LinkProviderString
End If
Next
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
ADOConnection.Close()
End Try

End Sub

I tried to shorten some lines of code so they won't wrap.

There is no security on these access databases as we created them
in-house as a front end query engine for our Oracle database.

Does anyone have any ideas?

[Tim Frawley]

Thank you for your quick response. When I posted the message I received
a DNS error after I submitted the post. I was not sure if this had gone
through.

I have resolved my problem. I finally realized I could not put the File
DSN link into the linked table string. This is what caused the error.

After looking through the properties for a linked table I realized that
a table that is linked through a file DSN was simply placing the driver
value and never looked at the file DSN again. I have since created the
code to update our Access databases using this approach and now we no
longer depend on a DSN at all.

The primary concern was the DSN held the driver versions we use for
Oracle. We have to standardize all our clients with the same driver but
that should be a small price to pay. :)
Thanks again,

Tim Frawley

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!