By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

(Re)link tables for multiple front end databases at same time

100+
P: 759
Hi !

Scenario:
3 front end databases based on the same back end database.

All front end parts are in the same folder (=> I know their paths) and have the same structure of linked tables.
One OR two OR all can be unlinked (at a tme) to the back end part.

What I know:
To relink a front end part to a back end part via VBA (no matter what path has the back end part).

What I have:
A very simple wizard to (re)link tables.
At this time this "wizard" is implemented in all three front end parts (I think that is not the best idea while I can design only once and use it from where I need, but I'll see later about)

What I wish to do:
When I run my wizard (from one of the front end part), after I (re)link tables (so I can save the path for the back end part), automatically do the same job for the other two front end parts.
The best will be WITHOUT open the other front end parts (to avoid run any code in this parts). If can't be avoid to open it I think I can develop code to skip the code which normally running when open it). Of course I need other code to close it after the wizard do the job.

Hope you understand what I mean.

Using other words, the title of this thread can be "How to run code from one database to another one (without open the second one)"

Thank you !
Nov 30 '11 #1

✓ answered by ADezii

This is how I interpret your Request:
  1. Open the External Database (FE) as defined by the Constant conEXTERNAL_DB within the context of the Active Database.
  2. The Table to be Linked may already exist in the External Database (FE), so DELETE it.
  3. From the context of the External Database conEXTERNAL_DB, Import the Table as defined by the Constant conTABLE_TO_LINK from the Back-end Database defined by conDB_TO_LINK.
  4. The following Code will Open the 'C:\Dezii\DB1.mdb' Database (simulated Front End), then import the Employees Table from the "C:\Dezii\Northwind.mdb" Database (simulated Back End) into it.
  5. This Code has been tested, and is fully operational.
    Expand|Select|Wrap|Line Numbers
    1. Dim appAccess As Access.Application
    2. Const conEXTERNAL_DB As String = "C:\Dezii\DB1.mdb"
    3. Const conDB_TO_LINK As String = "C:\Dezii\Northwind.mdb"
    4. Const conTABLE_TO_LINK As String = "Employees"
    5.  
    6. Set appAccess = CreateObject("Access.Application")
    7.  
    8. With appAccess    'Represents Access Front End Application
    9.   .OpenCurrentDatabase conEXTERNAL_DB
    10.   .DBEngine.Workspaces(0).Databases(0).TableDefs.Delete conTABLE_TO_LINK
    11.   .DoCmd.TransferDatabase acLink, "Microsoft Access", conDB_TO_LINK, acTable, _
    12.                                  conTABLE_TO_LINK, conTABLE_TO_LINK, False
    13. End With
    14.  
    15. appAccess.Quit
    16. Set appAccess = Nothing

Share this Question
Share on Google+
35 Replies


NeoPa
Expert Mod 15k+
P: 31,186
There is a DAO method called OpenDatabase() which allows you to open extra databases. Unlike OpenCurrentDatabase(), it doesn't require that the current database is closed to do it. I imagine you can work the rest out from there.
Nov 30 '11 #2

100+
P: 759
Thank you !
Nov 30 '11 #3

NeoPa
Expert Mod 15k+
P: 31,186
Always a pleasure to help Mihail :-)
Nov 30 '11 #4

ADezii
Expert 5K+
P: 8,597
This is how I interpret your Request:
  1. Open the External Database (FE) as defined by the Constant conEXTERNAL_DB within the context of the Active Database.
  2. The Table to be Linked may already exist in the External Database (FE), so DELETE it.
  3. From the context of the External Database conEXTERNAL_DB, Import the Table as defined by the Constant conTABLE_TO_LINK from the Back-end Database defined by conDB_TO_LINK.
  4. The following Code will Open the 'C:\Dezii\DB1.mdb' Database (simulated Front End), then import the Employees Table from the "C:\Dezii\Northwind.mdb" Database (simulated Back End) into it.
  5. This Code has been tested, and is fully operational.
    Expand|Select|Wrap|Line Numbers
    1. Dim appAccess As Access.Application
    2. Const conEXTERNAL_DB As String = "C:\Dezii\DB1.mdb"
    3. Const conDB_TO_LINK As String = "C:\Dezii\Northwind.mdb"
    4. Const conTABLE_TO_LINK As String = "Employees"
    5.  
    6. Set appAccess = CreateObject("Access.Application")
    7.  
    8. With appAccess    'Represents Access Front End Application
    9.   .OpenCurrentDatabase conEXTERNAL_DB
    10.   .DBEngine.Workspaces(0).Databases(0).TableDefs.Delete conTABLE_TO_LINK
    11.   .DoCmd.TransferDatabase acLink, "Microsoft Access", conDB_TO_LINK, acTable, _
    12.                                  conTABLE_TO_LINK, conTABLE_TO_LINK, False
    13. End With
    14.  
    15. appAccess.Quit
    16. Set appAccess = Nothing
Dec 1 '11 #5

100+
P: 759
Thank you very much ADezii.

I don't try yet but, after first view, I think that it is exactly what I am looking for.

I am almost sure that I return here only to choose the best answer :).
Dec 1 '11 #6

100+
P: 759
Hi again.

ADezii, your code work as I expect: very well.

But I wish to add more generality by looping through all FE tables or, for a full generality, through all FE linked objects.
I don't know how to "see" all this objects (I suspect that must be a collection somewhere. But where is it and how to extract it's items ?).
Can you point me to the right direction ?

Thank you !
Dec 1 '11 #7

100+
P: 759
Oh. Forget me, please. Sorry for that.

I see the solution in your code ADezii: TableDef. I think is the same for QueryDef.

However I can't find something similar for Reports.
Any advice ?
Dec 1 '11 #8

NeoPa
Expert Mod 15k+
P: 31,186
Mihail:
Using other words, the title of this thread can be "How to run code from one database to another one (without open the second one)"
I'm confused. I thought this was the question, which has already been answered.

@ADezii
I'm also confused that you would open a new version of Access to handle this when OpenDatabase() is available for use within the same session.
Also, the approach to delete and then recreate is not necessary (and can involve the loss of meta-data information) as there is already an article which deals with repointing and refreshing existing links. I only didn't include that because Mihail indicated he already knew how to handle that aspect of the question. For interest though, it's Relinking ODBC Tables using VBA and it includes relinking Access tables too.

I have no idea at this stage, why accessing reports comes into the scope of this question, but they can be found as the CurrentProject.AllReports() collection.

This all seems very strange to me, but I'm sure you two can sort things out between you. Please be careful of changing topics though, within the thread.
Dec 1 '11 #9

100+
P: 759
Sorry, NeoPa.

The title of the thread indeed is how to (re)link tables. I ask only that because I can't see far away at that moment. I know that you are able to change a little bit the title: "How to (Re)link front end databases to the back end databases" to give as more generality to this thread as ADezii (not me) can offer. Doing that you will eliminate the risk to become "out of subject".

Thank you !
Dec 1 '11 #10

NeoPa
Expert Mod 15k+
P: 31,186
No Mihail. I see you seem to be much confused by this. We are not looking to change the question to fit the answers. That would be somewhat ridiculous and of no help to those looking for a clear answer to a clear and similar question. The question is the question, and if a new question is required then you post a new one.

Rules are not invented purely to make your life less simple. They are there to ensure work done in these threads are not worthless to everyone else looking for similar solutions. Answers should match the question and be clearly posted in the thread. Attachments can be of value to enable users to experiment and learn to work with the ideas, but they are not a substitute for the answer - purely an addendum to one if posted.

There is always some leeway allowed for issues which are related, but when you get to a stage where a moderator (myself in this case) cannot even see the relation between the question and new posts, then it's gone too far off-topic.
Dec 1 '11 #11

ADezii
Expert 5K+
P: 8,597
I'm also confused that you would open a new version of Access to handle this when OpenDatabase() is available for use within the same session.
The OpenDatabase() Method returns a Reference to the Database object that represents it. This, unfortunately, this is not enough since we need a Reference to an Access Application Object in order to dynamically Link from an External Database (FE) to a Table in yet another External Database (BE). This is all being done within the context of the Current Database, which is not either the FE or BE DBs. The mechanism that is needed, as I see it, is:
Expand|Select|Wrap|Line Numbers
  1. <Access Application Object>.DoCmd.TransferDatabase()
P.S. - I can't see how this can be accomplished with OpenDatabase(), but if you can show me how this can be done, I would be very interested in seeing it.
Dec 1 '11 #12

ADezii
Expert 5K+
P: 8,597
Also, the approach to delete and then recreate is not necessary (and can involve the loss of meta-data information) as there is already an article which deals with repointing and refreshing existing links.
We are not talking about Linking from the Current Database, but from an External Database, referenced from the Current Database. Unless I am incorrect, these are 2 vastly different scenarios.
Dec 1 '11 #13

NeoPa
Expert Mod 15k+
P: 31,186
Well then, Sir ADezii of Philadelphia, As you've thrown down the gauntlet I must take up the challenge. It's not something I've done before, so it may take some time, but when I looked (to check it out for this thread) the pieces all seemed to fit together sensibly.

I'll see if I can knock up a scenario with four databases in the same foldere where :

A.MDB contains the code.
B.MDB has a table that initially starts as connected to C.MDB, but is changed, by the code in A.MDB such that the table links instead to D.MDB.

That is the challenge as I see it and I'll post back when I have an answer for you.
Dec 1 '11 #14

ADezii
Expert 5K+
P: 8,597
@NeoPa:
C.MDB, as I see it, is irrelevant, since we are not at all concerned with the Original Link, being it will be Deleted anyway based solely on the Object Name (Table). Good luck on the gauntlet. BTW, when was the last time you gauntleted (is that a word!)?
Dec 1 '11 #15

ADezii
Expert 5K+
P: 8,597
@NeoPa:
I've made several slight modifications to the Code in order to make it easier for testing purposes:
  1. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fLinkExternalFE_To_ExternalBE(strExternalFE As String, strExternalBE As String, _
    2.                                               strTableName As String) As Boolean
    3. On Error Resume Next
    4.  
    5. Dim appAccess As Access.Application
    6.  
    7. Set appAccess = CreateObject("Access.Application")
    8.  
    9. With appAccess    'Represents Access Front End Application
    10.   .OpenCurrentDatabase strExternalFE
    11.   .DBEngine.Workspaces(0).Databases(0).TableDefs.Delete strTableName
    12.   .DoCmd.TransferDatabase acLink, "Microsoft Access", strExternalBE, acTable, _
    13.                                    strTableName, strTableName, False
    14. End With
    15.  
    16. 'Use the Error Object's Number Property to determine whether or not there was an Error
    17. fLinkExternalFE_To_ExternalBE = (Err.Number = 0)
    18.  
    19. If Not appAccess Is Nothing Then
    20.   appAccess.Quit
    21.   Set appAccess = Nothing
    22. End If
    23. End Function
  2. Sample Call to Function:
    Expand|Select|Wrap|Line Numbers
    1. If Not fLinkExternalFE_To_ExternalBE("C:\Test\FE.mdb", "C:\Test\BE.mdb", "Employees") Then
    2.   MsgBox "Unable to perform External Link!", vbCritical, "Link Error"
    3. Else
    4.   MsgBox "Link successful", vbInformation, "External Link Completed"
    5. End If
  3. Code Execution within Test.mdb.
Dec 1 '11 #16

NeoPa
Expert Mod 15k+
P: 31,186
The following code (found in frmABCDTest of A.MDB) seems to do the job. Try it out and see what you think :

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdTest_Click()
  5.     Dim strB As String, strC As String, strD As String
  6.  
  7.     With CurrentProject
  8.         strB = .Path & "\B.MDB"
  9.         strC = .Path & "\C.MDB"
  10.         strD = .Path & "\D.MDB"
  11.     End With
  12.     With OpenDatabase(Name:=strB)
  13.         With .TableDefs("tblB")
  14.             If Right(.Connect, 5) = "C.MDB" Then
  15.                 .Connect = ";DATABASE=" & strD
  16.             Else
  17.                 .Connect = ";DATABASE=" & strC
  18.             End If
  19.             Call .RefreshLink
  20.         End With
  21.         Call .Close
  22.     End With
  23. End Sub
  24.  
  25. Private Sub cmdExit_Click()
  26.     Call DoCmd.Close
  27. End Sub
PS. I'm attaching the databases I used for you to test / play with.
Attached Files
File Type: zip ReLink.Zip (390.2 KB, 76 views)
Dec 1 '11 #17

NeoPa
Expert Mod 15k+
P: 31,186
ADezii:
C.MDB, as I see it, is irrelevant, since we are not at all concerned with the Original Link, being it will be Deleted anyway based solely on the Object Name (Table).
I have no idea where that came from, but it wasn't in the original question. In fact the original question was reasonably clear about wanting to relink existing tables to a similar, but different, back-end file. This is my main reason for querying the approach of deleting the link in the first place. it's not in accordance with the question (although if there were no alternative it would make a good work-around - which is what I assumed you were going for originally).

ADezii:
Good luck on the gauntlet. BTW, when was the last time you gauntleted (is that a word!)?
That would be 'Challenged' (and not educationally before you say anything :-D). Thank you. I believe I have posted proof now that the concept is sound.
Dec 1 '11 #18

NeoPa
Expert Mod 15k+
P: 31,186
FYI: As an interesting side-issue, I also found a property called .SourceTableName which reflected, but did not allow changing of, the name of the destination table. I had originally intended to name the two tables [tblC] and [tblD] to make the situation as clear as possible, but I was disallowed from doing that unfortunately.
Dec 1 '11 #19

ADezii
Expert 5K+
P: 8,597
One OR two OR all can be unlinked (at a tme) to the back end part.

What I know:
To relink a front end part to a back end part via VBA (no matter what path has the back end part).
Won't your Code crash under either of these 2 circumstances?
Expand|Select|Wrap|Line Numbers
  1. Dim strB As String, strC As String, strD As String
Didn't you tell me a long time ago that this is a NO-NO! (LOL)
Dec 1 '11 #20

NeoPa
Expert Mod 15k+
P: 31,186
ADezii:
Won't your Code crash under either of these 2 circumstances?
My code is merely a proof of concept. Not a full-blown solution for Mihail I'm afraid.

ADezii:
Didn't you tell me a long time ago that this is a NO-NO! (LOL)
Very possibly :-D, though I'm not sure what you're saying I said that about. It may help to clarify (although strB is not the same as just B of course) that these names refer to the concepts and database files which are B.MDB, C.MDB and D.MDB of course. I felt that was the easiest nomenclature to work with and understand in this particular circumstance. I'm very curious still, to know what it was that I said to you then (I know I've said a number of things over the years but cannot remember anything that would pertain to this exactly). Of course, it's not impossible I'm being inconsistent, but I certainly hope not. Such consistency is so important I believe.
Dec 1 '11 #21

ADezii
Expert 5K+
P: 8,597
@NeoPa:
Only kidding of course, but I was referring to the Multiple Declarations on a single Code Line, as in:
Expand|Select|Wrap|Line Numbers
  1. Dim strB As String, strC As String, strD As String 
Dec 2 '11 #22

NeoPa
Expert Mod 15k+
P: 31,186
I am always happy for you to have digs ADezii. Never a problem (I do enough back after all) :-)

In this case I suspect there must be some confusion. Let me explain :
  1. I advise against multiple declarations on a line if they are not all the same type.
  2. I warn against multiple declarations of the same (intentional) type. This is easier to show with an example :
    Expand|Select|Wrap|Line Numbers
    1. Dim strA, strB, strC As String
    Many, mistakenly, assume that is declaring all variables as strings but it's not.
  3. I advise against allowing the declaration line (and all other lines too actually) to exceed 80 chars so that it is easier to read when printed or when viewing on a standard screen.
The code I used here though, is my usual style. When I have multiple variables of the same type and they can fit within the 80 columns I keep them on the same line.
Dec 2 '11 #23

100+
P: 759
A little bit of work around yours code, guys.
Only fore more generality.

The code in form frmABCDTest (A.MDB):
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdTest_Click()
  5. On Error GoTo ErrorHandler
  6.  
  7. Dim BackEndDatabase As String
  8.     BackEndDatabase = SelectBackEndDatabase(CurrentProject.Path)
  9. If BackEndDatabase = "" Then Exit Sub
  10.  
  11. '(Re)link current Front End database (A.MDB)
  12. Dim tbl As TableDef
  13.     With CurrentDb
  14.         For Each tbl In .TableDefs
  15.             If IsLinked(tbl.Name) Then
  16.                 With .TableDefs(tbl.Name)
  17.                     .Connect = ";DATABASE=" & BackEndDatabase
  18.                     Call .RefreshLink
  19.                 End With
  20.             End If
  21.         Next
  22.     End With
  23.  
  24. '(Re)link other(s) Front End database(s) (B.MDB)
  25. Dim strB As String
  26.     strB = CurrentProject.Path & "\B.MDB"
  27.  
  28.     With OpenDatabase(Name:=strB)
  29.         For Each tbl In .TableDefs
  30.             If IsLinked(tbl.Name) Then
  31.                 With .TableDefs(tbl.Name)
  32.                     .Connect = ";DATABASE=" & BackEndDatabase
  33.                     Call .RefreshLink
  34.                 End With
  35.             End If
  36.         Next
  37. Ex:
  38.         Call .Close
  39.     End With
  40. Exit Sub
  41.  
  42. ErrorHandler:
  43.     MsgBox ("I think you select a wrong Back End database. Try again")
  44.     Resume Ex
  45. End Sub
  46.  
  47. Function IsLinked(TableName As String) As Boolean
  48. '(Non-linked tables have a type of 1, tables linked using ODBC have a type of
  49. '4 and all other linked tables have a type of 6)
  50.     IsLinked = Nz(DLookup("Type", "MSysObjects", _
  51.                 "Name = '" & TableName & "'"), 0) <> 1
  52. End Function
  53.  
  54. Private Sub cmdExit_Click()
  55.     Call DoCmd.Close
  56. End Sub
The code in a global module modPublic (also in A.MDB)
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
  5. "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
  6.  
  7. Private Type OPENFILENAME
  8.     lStructSize As Long
  9.     hwndOwner As Long
  10.     hInstance As Long
  11.     lpstrFilter As String
  12.     lpstrCustomFilter As String
  13.     nMaxCustFilter As Long
  14.     nFilterIndex As Long
  15.     lpstrFile As String
  16.     nMaxFile As Long
  17.     lpstrFileTitle As String
  18.     nMaxFileTitle As Long
  19.     lpstrInitialDir As String
  20.     lpstrTitle As String
  21.     flags As Long
  22.     nFileOffset As Integer
  23.     nFileExtension As Integer
  24.     lpstrDefExt As String
  25.     lCustData As Long
  26.     lpfnHook As Long
  27.     lpTemplateName As String
  28. End Type
  29.  
  30. Public Function SelectBackEndDatabase(StartFolder As String) As String
  31.     Dim OpenFile As OPENFILENAME
  32.     Dim lReturn As Long
  33.     Dim sFilter As String
  34.     OpenFile.lStructSize = Len(OpenFile)
  35. '    OpenFile.hwndOwner = strform.Hwnd
  36.     sFilter = "Access 2003 (*.mdb)" & Chr(0) & "*.mdb" & Chr(0) & _
  37.       "Access 2007 (*.accdb)" & Chr(0) & "*.accdb" & Chr(0)
  38.     OpenFile.lpstrFilter = sFilter
  39.     OpenFile.nFilterIndex = 1
  40.     OpenFile.lpstrFile = String(257, 0)
  41.     OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
  42.     OpenFile.lpstrFileTitle = OpenFile.lpstrFile
  43.     OpenFile.nMaxFileTitle = OpenFile.nMaxFile
  44.     OpenFile.lpstrInitialDir = "StartFolder"
  45.     OpenFile.lpstrTitle = "Select a file using the Common Dialog DLL"
  46.     OpenFile.flags = 0
  47.     lReturn = GetOpenFileName(OpenFile)
  48.         If lReturn = 0 Then
  49.             MsgBox "A file was not selected!", vbInformation, "Select a file using the Common Dialog DLL"
  50.          Else
  51.             SelectBackEndDatabase = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
  52.          End If
  53. End Function
Attached Files
File Type: zip ReLinkFE.zip (449.8 KB, 71 views)
Dec 11 '11 #24

100+
P: 759
Hi, again !

@NeoPa
Your code work fine with one exception. Just now I see that.

If B.MDB contain an UN_linked table, the Function IsLinked return TRUE for this table.
My tests indicates that
Expand|Select|Wrap|Line Numbers
  1. Nz(DLookup("Type", "MSysObjects", _
  2.                 "Name = '" & TableName & "'"), 0)
return ZERO in this case.

I change a little bit this:
Expand|Select|Wrap|Line Numbers
  1. IsLinked = Nz(DLookup("Type", "MSysObjects", _
  2.                 "Name = '" & TableName & "'"), 0) <> 1
replacing with this:
Expand|Select|Wrap|Line Numbers
  1. IsLinked = Nz(DLookup("Type", "MSysObjects", _
  2.                 "Name = '" & TableName & "'"), 0) > 1
Now, the code works (again) fain. But, based on my skill, I am not sure that this is the best approach.
This is the reason I inform you (all) about.
Dec 18 '11 #25

100+
P: 759
More:
If the FE is linked to a BE the IsLinked function return TRUE for tables which are linked. Very well.
But, if the BE is renamed or moved this function is not useful because the tables are no more linked.
So I need a function to determine if a table MUST be linked (not if it IS linked).

I review all codes in this thread.
Maybe I am wrong but nothing seems to help me to determine if a table (in current FE database) must be linked to BE or the current database is it's parent.

Can you (all) help me with this ? (to define a function MustBeLinked(TableName As String) As Boolean)

Thank you !
Dec 18 '11 #26

100+
P: 759
Finally, this code work.

And is more flexible than I ask in the top post:
1) No need that FE to be in the same folder;
2) No need that FEs to have the same linked tables.

Of course, any proposal for improvement is welcome.
Expand|Select|Wrap|Line Numbers
  1. Public Function ReConnect(BackEndPath As String) As Boolean
  2.     ReConnect = False
  3. On Error GoTo ErrorHandler
  4. If BackEndPath = "" Then Exit Function
  5.  
  6. Dim DB As Database
  7. '(Re)link current Front End database
  8.     Set DB = CurrentDb
  9.     Call RelinkThisFE(DB, BackEndPath)
  10.  
  11. '(Re)link other(s) Front End database(s)
  12. Dim FEfolder, FEname As String, FEpath As String
  13.     'First of my FE, named Calcul.accdb
  14.     FEname = "\Calcul.accdb"
  15.         FEfolder = CurrentProject.Path
  16.         FEpath = FEfolder & FEname
  17.         Set DB = OpenDatabase(Name:=FEpath)
  18.     Call RelinkThisFE(DB, BackEndPath)
  19.  
  20.     'Second of my FE, named Citiri.accdb
  21.     FEname = "\Citiri.accdb"
  22.         FEfolder = CurrentProject.Path
  23.         FEpath = FEfolder & FEname
  24.         Set DB = OpenDatabase(Name:=FEpath)
  25.     Call RelinkThisFE(DB, BackEndPath)
  26.  
  27.     'All is Ok
  28.     ReConnect = True
  29.  
  30. Ex:
  31. Exit Function
  32.  
  33. ErrorHandler:
  34.     MsgBox ("I think you select a wrong Back End database. Try again")
  35.     DB.Close
  36.     Resume Ex
  37. End Function
  38.  
  39. Private Sub RelinkThisFE(DB As Database, BackEndPath As String)
  40. Dim tbl As TableDef
  41.     With DB
  42.         For Each tbl In .TableDefs
  43.             If (tbl.Attributes And dbAttachedTable) Then 'This is a linked table
  44.                 With .TableDefs(tbl.Name)
  45.                     .Connect = ";DATABASE=" & BackEndPath
  46.                     Call .RefreshLink
  47.                 End With
  48.             End If
  49.         Next
  50.         Call .Close
  51.     End With
  52. End Sub
Thank you again for all your help !
Dec 19 '11 #27

NeoPa
Expert Mod 15k+
P: 31,186
Mihail:
Your code work fine with one exception. Just now I see that.

If B.MDB contain an UN_linked table, the Function IsLinked return TRUE for this table.
My tests indicates that
The code associated with this example is posted in post #17 (The one with the attachment). It doesn't include any IsLinked function, so I'm not clear why you're querying it with me.
Dec 19 '11 #28

NeoPa
Expert Mod 15k+
P: 31,186
Mihail:
So I need a function to determine if a table MUST be linked (not if it IS linked).
I'm not sure I follow this at all. A linked table may not be linked to any specific table, but a table is linked if the .Connect property is greater than an empty string. Standard tables have a .Connect property of an empty string.

If this doesn't answer your question then please ask it again but I'll need to understand what you want to know if I'm to help.
Dec 19 '11 #29

NeoPa
Expert Mod 15k+
P: 31,186
I have no idea why anyone, at this stage of the VBA language, would ever use GoSubs. I can't think of anything to say about this that wouldn't sound insulting, so I'll leave that with you.

If you want to use the Attributes of a TableDef property to determine whether or not it is linked or otherwise (or more specifically a linked Jet table) then you need to compare it differently (Your code will give an incorrect result if other attributes are set as well as the one you are checking for.) :

Your line #52 should be :
Expand|Select|Wrap|Line Numbers
  1.     IsLinkedTable = (tbl.Attributes And dbAttachedTable)
PS. You will notice I use tbl in place of DB.TableDefs(tbl.Name), as they are synonymous and refer to exactly the same object.
PPS. The value returned will never be True, but only Not False. This will be fine in your code, but if required a True value can result from comparing the returned value with False.
Dec 19 '11 #30

100+
P: 759
Hope you forget me (again).
I played a lot around this code.
From somewhere I got a piece of code (a function IsLinked()) and I think that is from you because your code was the heavy piece for me.

Any way the job was completely done (see post #27).

Thank you again (I refer you and ADezii) for all the help.
I learn a lot from you both. Even a new English word: gauntlet :)
Dec 19 '11 #31

NeoPa
Expert Mod 15k+
P: 31,186
Did you see my post #30 which suggests a better version of line #52, (the origanal of) which will not work correctly in all circumstances?
Dec 19 '11 #32

100+
P: 759
Now I see. Thank you for this.

I explain why I use GoSub instead to apply a function: to keep all code in one place. (In order to reuse this routine at once).

Can you explain better your view point for first PS ?
I think that in line #52 (now, after I edit my post line #52 become line #51) tbl do not refer the same object any time.
This line is applied from line #38. But line #38 is applied from line #19 and #26.
For line #19 the database (DB) is Calcul.accdb (Compute.accdb in English).
But for line # 26 the database (DB) is Citiri.accdb (Reads.accdb in English).
So the DB must be explicitly referenced because it is not the same any time.

Hope I understand well what you wish to say in the second PS: The value will never be -1 but only <> 0.
Dec 19 '11 #33

NeoPa
Expert Mod 15k+
P: 31,186
Mihail, I cannot image what I can say to convince you why using GoSub is a bad idea. If you even need to be told then there is very little chance you'd understand. It is enough to say that it makes the code very much more difficult to maintain and read.

Line #37 ensures that tbl is always set to one of the TableDefs from whatever database DB is currently set to (Regardless of where RelinkThisFE is called from). Therefore DB.TableDefs(tbl.Name) is saying get me the table from the DB database whose name matches the table I already have from the DB database. As two tables cannot have the same name this is getting tbl.

Mihail:
Hope I understand well what you wish to say in the second PS: The value will never be -1 but only <> 0.
What you say is true, but not exactly the point I was trying to get across.
Expand|Select|Wrap|Line Numbers
  1. If {Boolean Expression} Then {Statement}
This code will execute {Statement} whenever {Boolean Expression} is not False (or <> 0). Even though {Boolean Expression} is not True (in this case), it still triggers the True path when checked.
Dec 20 '11 #34

100+
P: 759
Done, NeoPa !
I (re)edit post #27
Is this look better ?
Dec 20 '11 #35

NeoPa
Expert Mod 15k+
P: 31,186
That's a very easy question. Yes.
Dec 21 '11 #36

Post your reply

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