473,573 Members | 2,829 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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.Prope rties("Jet OLEDB:Link Provider String").Value string
and then I try to set ADOXTable.Prope rties("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.s et_Value(Object pVal)
at test.frmTest.Re freshLinkedTabl esWithADOX() in frmTest.vb:line
302

This is the Code:

Sub RefreshLinkedTa blesWithADOX()

Dim ADOXTable As New ADOX.Table
Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connectio n
Dim LinkProviderStr ing As String

Try

ADOConnection.O pen("Provider=M icrosoft.Jet.OL EDB.4.0;" & _
"Data Source=e:\My Documents\db1.m db;" & _
"Jet OLEDB:Engine Type=4;")

ADOXCatalog.Act iveConnection = ADOConnection

For Each ADOXTable In ADOXCatalog.Tab les
If ADOXTable.Type = "PASS-THROUGH" Then
LinkProviderStr ing = ADOXTable.Prope rties("Jet OLEDB:Link " &
_
Provider String").Value
'''''''''
' Manipulate LinkProviderStr ing to replace DSN, UID, PWD and
DBQ
' for new DSN values.
'''''''''
ADOXTable.Prope rties("Jet OLEDB:Link " & _
"Provider String").Value = LinkProviderStr ing
End If
Next
Catch ex As Exception
MessageBox.Show (ex.ToString)
Finally
ADOConnection.C lose()
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?
Nov 21 '05 #1
2 8648
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*********@fi shgame.state.ak .us> schreef in bericht
news:bf******** *************** **@posting.goog le.com...
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.Prope rties("Jet OLEDB:Link Provider String").Value string
and then I try to set ADOXTable.Prope rties("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.s et_Value(Object pVal)
at test.frmTest.Re freshLinkedTabl esWithADOX() in frmTest.vb:line
302

This is the Code:

Sub RefreshLinkedTa blesWithADOX()

Dim ADOXTable As New ADOX.Table
Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connectio n
Dim LinkProviderStr ing As String

Try

ADOConnection.O pen("Provider=M icrosoft.Jet.OL EDB.4.0;" & _
"Data Source=e:\My Documents\db1.m db;" & _
"Jet OLEDB:Engine Type=4;")

ADOXCatalog.Act iveConnection = ADOConnection

For Each ADOXTable In ADOXCatalog.Tab les
If ADOXTable.Type = "PASS-THROUGH" Then
LinkProviderStr ing = ADOXTable.Prope rties("Jet OLEDB:Link " &
_
Provider String").Value
'''''''''
' Manipulate LinkProviderStr ing to replace DSN, UID, PWD and
DBQ
' for new DSN values.
'''''''''
ADOXTable.Prope rties("Jet OLEDB:Link " & _
"Provider String").Value = LinkProviderStr ing
End If
Next
Catch ex As Exception
MessageBox.Show (ex.ToString)
Finally
ADOConnection.C lose()
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?

Nov 21 '05 #2


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!
Nov 21 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
3742
by: Wolfgang Kaml | last post by:
Hello All, I have been working on this for almost a week now and I haven't anything up my sleeves anymore that I could test in addition or change.... Since I am not sure, if this is a Windows 2003 Server or ADO or ODBC issue, I am posting this on all of the three newsgroups. That's the setup: Windows 2003 Server with IIS and ASP.NET...
6
6758
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:
11
17540
by: DJJ | last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL tables to a Microsoft Access 2003 database. I am finding that the data from the MySQL tables takes a hell of a long time to load making any kind linkage with my Access data virtually useless. I have the MySQL driver setup in as a USER DSN. The MySQL data is...
0
3678
by: Mike Knight | last post by:
I have the following code in an Excel 2003 module that creates a query in MS Access 2003. The created query can then be opened from Access. In Access, right-mouse clicking in design mode, in properties, the ODBC timeout setting is blank. How can I set this from Excel's VBA code when "MainProgram" is executed? (Commandtimeout does not...
6
2698
by: Claudia Fong | last post by:
Hello, I'm using the sql statement below to create a new table from an old one. But I found a little problem with that. In my old table DEP2004, I have one field's property allow zero length is YES, but after creating the new table DEP2005, this property change to NO. It means it won't allow zero lenght.. How can I change this property...
11
4588
by: DraguVaso | last post by:
Hi, I want to make a small application in VB.NET that relinks all the query's and tables in an Access database that are linked via ODBC to an SQL Server. It must be able to relink all the tables and query's to a given databse, on a given sql server with given login and password. Aybody knows how to do that, or better: has a sample...
1
3793
by: RLN | last post by:
RE: Access 2003 using WinXP SP2 Problem: When I start up my app I double click either one of my two Oracle tables in the table list, it asks for an id and pass. I need them to be linked at startup automatically. The Oracle db contains two tables I need. I've already created an ODBC data source that contains the userid and pass to link...
1
5395
by: mikerudy | last post by:
I have an 3rd-party application that uses a SQL back-end, but uses Access 2000 (linked tables all using the same DSN) as an intermediary. We recently upgraded from SQL 7 to SQL 2005, which went fine. Now, however, we want to setup SQL Mirroring to provide automatic failover. The mirroring works fine. In my test environment, the mirror...
3
4915
by: Vee007 | last post by:
Following is my code: Dim objCatalog As ADOX.Catalog Dim objTableLink As ADOX.Table Dim objADOConnection As ADODB.Connection Try objADOConnection = New ADODB.Connection objADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Database\abc.mdb;User Id=admin;Password=;") ...
0
7781
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7699
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7788
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6421
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5594
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5292
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3733
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2216
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1304
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.