473,322 Members | 1,504 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Relinking ODBC Tables using VBA example

I have tested the code below but would like a working example.

Ref: Relinking ODBC Tables using VBA
I have tested this but it does not seem to work for me. My question: Is the strDBName the database name I have to modify to suit my main database? Like "myAccess.mdb"

Is it rude to ask for an example mdb?
Comment NeoPa
Expand|Select|Wrap|Line Numbers
  1. 'ReLink() Updates links of all tables that currently link to strDBName to point
  2. 'to strDBName in the strFolder folder (if specified, otherwise the same folder
  3. 'as the current database).
  4. Public Sub ReLink(ByVal strDBName As String, _
  5.                   Optional ByVal strFolder As String = "")
  6.     Dim intParam As Integer, intErrNo As Integer
  7.     Dim strOldLink As String, strOldName As String
  8.     Dim strNewLink As String, strMsg As String
  9.     Dim varLinkAry As Variant
  10.     Dim db As DAO.Database
  11.     Dim tdf As DAO.TableDef
  12.  
  13.     Set db = CurrentDb()
  14.     If strFolder = "" Then strFolder = CurrentProject.Path
  15.     If Right(strFolder, 1) = "\" Then _
  16.         strFolder = Left(strFolder, Len(strFolder) - 1)
  17.     strNewLink = strFolder & "\" & strDBName
  18.     For Each tdf In db.TableDefs
  19.         With tdf
  20.             If .Attributes And dbAttachedTable Then
  21.                 varLinkAry = Split(.Connect, ";")
  22.                 For intParam = LBound(varLinkAry) To UBound(varLinkAry)
  23.                     If Left(varLinkAry(intParam), 9) = "DATABASE=" Then Exit For
  24.                 Next intParam
  25.                 strOldLink = Mid(varLinkAry(intParam), 10)
  26.                 If strOldLink <> strNewLink Then
  27.                     strOldName = Split(strOldLink, _
  28.                                        "\")(UBound(Split(strOldLink, "\")))
  29.                     If strOldName = strDBName Then
  30.                         varLinkAry(intParam) = "DATABASE=" & strNewLink
  31.                         .Connect = Join(varLinkAry, ";")
  32.                         On Error Resume Next
  33.                         Call .RefreshLink
  34.                         intErrNo = Err.Number
  35.                         On Error GoTo 0
  36.                         Select Case intErrNo
  37.                         Case 3011, 3024, 3044, 3055, 7874
  38.                             varLinkAry(intParam) = "DATABASE=" & strOldLink
  39.                             .Connect = Join(varLinkAry, ";")
  40.                             strMsg = "Database file (%F) not found.%L" & _
  41.                                      "Unable to ReLink [%T]."
  42.                             strMsg = Replace(strMsg, "%F", strNewLink)
  43.                             strMsg = Replace(strMsg, "%L", vbCrLf)
  44.                             strMsg = Replace(strMsg, "%T", .Name)
  45.                             Call MsgBox(Prompt:=strMsg, _
  46.                                         Buttons:=vbExclamation Or vbOKOnly, _
  47.                                         Title:="ReLink")
  48.                             If intErrNo = 3024 _
  49.                             Or intErrNo = 3044 _
  50.                             Or intErrNo = 3055 Then Exit For
  51.                         Case Else
  52.                             strMsg = "[%T] relinked to ""%F"""
  53.                             strMsg = Replace(strMsg, "%T", .Name)
  54.                             strMsg = Replace(strMsg, "%F", strNewLink)
  55.                             Debug.Print strMsg
  56.                         End Select
  57.                     End If
  58.                 End If
  59.             End If
  60.         End With
  61.     Next tdf
  62. End Sub
Kind regards,
Mario
Oct 29 '19 #1
2 1715
NeoPa
32,556 Expert Mod 16PB
Hi Mario.

Very glad to see you're here and have posted a question. Welcome to Bytes.com.

I will need to delete the question you posted in the linked article thread as the questions must be posted in question threads rather than over there - but before that I'll copy across anything relevant so nothing is actually lost.

The first thing we probably need to know is what happened when it didn't work for you? This was written a fair while ago so I'll need to re-read it to determine what it's about myself. If you can explain what happened that was different from what you were expecting then we can see if the procedure or your expectations are in error.

KyooMario:
Is the strDBName the database name I have to modify to suit my main database? Like "myAccess.mdb"
No. Read further down for more background but that is for identifying the linked tables already connected to a database with that name.

KyooMario:
Is it rude to ask for an example mdb?
When you put it that way then I'm inclined to see what I can do. It may not be the best way to go though. Before I could even start on a working example DB for you I'd need a better understanding of what you desire and expect. I don't have that yet.

I expect that when we get to that point in the process you won't need it done for you separately but you'll simply copy and paste the code into your own database and find it works.

The original was simply for relinking existing tables and making sure if the table is found in a different place then it will still work. It doesn't include logic to change the database name per se.

If that sounds weird then let me explain the scenario. We have users split across multiple RDP servers. Sometimes the linked tables have to use UNC addresses to get to the same table that, at other times, is accessed via C:\... This code handles switching safely and seamlessly.

If it's easier, you could specify, clearly and precisely, exactly what it is you're hoping the procedure will do for you. In that case I could probably amend it for you pretty eeasily to do that. Does that sound like a preferable approach? Do bear in mind that ODBC linked tables are treated differently. I also have code for them but it's quite different.
Oct 30 '19 #2
NeoPa
32,556 Expert Mod 16PB
Just to follow up on that, I've just noticed that your title refers to ODBC-linked tables. This code deals specifically with Jet-linked tables. Can you include with your next reply a clear indication of which type it is that you're interested in.

As I mentioned earlier, I have worked with, and have examples of, both. We need to understand what it is you want before we can help you though, of course.
Oct 30 '19 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

6
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...
10
by: Hank1234 | last post by:
Can I use one Data Adapter and one Command Builder to update amny tables? Currently in my data adapter I query two tables and fill them into two tables in a data set. When I make a change to a...
0
by: mcast | last post by:
How do I modify Tables/Database's though ODBC without using Dynasets? Can point me in the right direction or show me some sample code. I'm new to Visual C++ programming.
11
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...
2
by: SKB | last post by:
Hi, I am absolutely new to this area. I am getting the following difficulty : Access denied for user 'ODBC'@'localhost' (using password: NO) when I try the mysql command from within the...
0
by: jrhowcroft | last post by:
I have a number of databases in MS Access 97. Some tables in these databases are linked to ODBC tables in Oracle9/10. We would like to change the user name and password on the Oracle tables so each...
10
by: Richard | last post by:
Hi folks, thanks for taking the time to read this (and hopefully point our where I'm going wrong). The scenario: I have a local Access2007 database which links in several read only mySql...
8
MMcCarthy
by: MMcCarthy | last post by:
As a lot of my projects involve using an odbc linked backend data source I have found this piece of code to be very useful. I usually trigger it to run on system startup like in AutoExec macro. It...
81
MMcCarthy
by: MMcCarthy | last post by:
Hi everyone I am using this as a test question to test the new Rate up/down feature for threads and posts. For anyone looking for a legitimate answer to this question please check out this...
3
by: pradeepatta | last post by:
We have an ms access application and tables in that are linked with backend database SQL SERVER 2008 I used to manually link the access tables with SQL tables with the help of 'Table Link Manager'...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.