473,847 Members | 1,472 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 8661
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
3777
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 actiavted Access 2002 mdb file (and yes, proper rights are set on TMP paths and path,
6
6792
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
17588
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 sitting out on a server and the Access database is running locally. The network connection is very...
0
3692
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 set this property) '=============================================================================
6
2706
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 in C#?
11
4617
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 application doing this? Thanks in advance!
1
3821
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 to the Oracle DB. It works. When I go into linked table manager, I can link to them just fine
1
5410
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 server becomes active automatically when the service is stopped on the principal server. I can see...
3
4929
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=;") objCatalog = New ADOX.Catalog objCatalog.ActiveConnection = objADOConnection ...
0
9892
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10991
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10653
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10718
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
9490
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7888
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7061
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
4129
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3168
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.