Dont know how "Do while" works (1 Viewer)

genesis

Registered User.
Local time
Today, 15:36
Joined
Jun 11, 2009
Messages
205
Good day to all,

I have this attached picture "paths.jpg"

the purpose of this paths is for easy linking procedure. the form is a countinous form which has only three fields: pathnameid,pathname,pathlocation

And I have this code:

'-------------------------------------------------
Private Sub attach_Click()

Dim wrk As Workspace
Dim dbBack As Database
Dim strTable As String
Dim strFileName As String
Set wrkDefault = DBEngine.Workspaces(0)


strFileName = DLookup("pathlocation", "path", "pathnameid = 1")
Set dbBack = wrkDefault.OpenDatabase(strFileName, False, False, "MS Access;PWD=""")
DoCmd.TransferDatabase acLink, "Microsoft Access", dbBack.Name, acTable, "companybranch", "companybranch", False, True

DoCmd.close acForm, "path", acSaveNo
MsgBox "Connection complete!", vbInformation, ""

End Sub
'---------------------------------------------------

actually it is not complete yet and is not what I need because it is lacking.

as you can see, when I click the Attach Button, only PathID Number 1 only will be attached because it is what in my strfilename criteria.

What I would like to achieve is:

after I have attached the database with pathID #1, use the database path and name, locate all the tables present on that database and link it.

then, it will go to the next record that is pathID#2 and attach it and so on so forth up to the last pathID.


I was thinking that Do while statement would be the solution but I dont know how to use it to achieve what I need to do!

Kindly help me out again.
 

Attachments

  • paths.JPG
    paths.JPG
    99.3 KB · Views: 124

genesis

Registered User.
Local time
Today, 15:36
Joined
Jun 11, 2009
Messages
205
Private Sub attach_Click()

dim rst as DAO.Recordset
Dim wrk As Workspace
Dim dbBack As Database
Dim strTable As String
Dim strFileName As String
Set wrkDefault = DBEngine.Workspaces(0)
set rst = currentdb.openrecordset("path")

rst.movefirst
do until rst.EOF
strFileName = rst("PathLocation")
Set dbBack = wrkDefault.OpenDatabase(strFileName, False, False, "MS Access;PWD=""")
DoCmd.TransferDatabase acLink, "Microsoft Access", dbBack.Name, acTable, "companybranch", "companybranch", False, True
rst.movenext
loop

DoCmd.close acForm, "path", acSaveNo
MsgBox "Connection complete!", vbInformation, ""

rst.close
set rst = nothing

End Sub
-----------------------------

the above code was a reply from me but the docmd.transferdatabase line there was only applicable for one database name companybranch with table name companybranch also. what about for other database with other table names?
 

genesis

Registered User.
Local time
Today, 15:36
Joined
Jun 11, 2009
Messages
205
Private Sub cmdTables_Click()
Dim obj As AccessObject, dbs As Object

Set dbs = Application.CurrentData

' Check each object of the AllTables collection
For Each obj In dbs.AllTables
' When you find a table, display its name
MsgBox obj.Name
Next obj
End Sub

;-------------------------------

I got this code from googling. How can I incorporate that to the attach code to achieve my goal?
 

genesis

Registered User.
Local time
Today, 15:36
Joined
Jun 11, 2009
Messages
205
Dim db As Database

Dim Tbl As TableDef
Dim TblNames As String
Dim TblCount As Integer
Set db = CurrentDb

TblCount = 0

For Each Tbl In db.TableDefs
If Tbl.Attributes = 0 Then 'Ignores System Tables
TblNames = Tbl.Name

End If
Next Tbl

----------------------------------------

Now with the above code, where can I insert this code so that

after I have attached the database with pathID #1, use the database path and name, it will use the above code to locate all the tables present on that database and use those tables to link it.

then, it will go to the next record that is pathID#2 and attach it and so on so forth up to the last pathID.


Kindly assist please.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 23:36
Joined
Sep 12, 2006
Messages
14,490
i think most people would link to a database by managing a new tabledef item, and inserting it into the databases tabledefs collection

i actually store a local table in my database identifying the path/filename/tablename of each backend data table i want to connect, and my routine iterates this table, and manages the tabledef object for each item. this way you can use different back ends for different linked tables, and its easy to add new tables to the lined table list

i know boblarson has provided a good table relinker on this site - i am not sure exactly what mechanism he uses, but its probably something similar - try that.
 

DCrake

Remembered
Local time
Today, 23:36
Joined
Jun 8, 2005
Messages
8,634
I don't know how your databases are organised but what is the reason for having so many back ends, not to say you can't. To me the best solution would be to manually create a new mdb and perform the linked tables manually linking all tables in all mdb's in to the new mdb. Then from your front end link to the gateway backend. also are you not going to get into trouble with naming conventions.

David
 

genesis

Registered User.
Local time
Today, 15:36
Joined
Jun 11, 2009
Messages
205
thanks for the reply Dave but I have seen relinker of several sites but it was not enough for my project though I just studied their code.

DCrake the purpose of several backends is because I am foreseeing the each database file to get big and for the purposes of replicating the data to other office/store/branch.

Anyway, I have studied the code provided to me with different forums and thru googling and I have managed to create my very own relinker with stating the table objects name.

My new problem right now would be:

I have this database and it has local and linked tables. what I want to do is to decipher thru vba what tables types are " linked " and by that criteria, loop thru those objects and delete them.

I am going to post new topic for this on the Module Section. Hope somebody will help.
 

dfenton

AWF VIP
Local time
Today, 18:36
Joined
May 22, 2007
Messages
469
I'm not sure if it's what you're looking for, but my Reconnect utility might be exactly what you're looking for:

http://dfenton.com/DFA/download/Access/Reconnect.html

Because almost all of my apps have a back-end data file and a tmp.mdb stored on the user workstation (not always in the same folder as the front end), I find it quite useful. It presents a list of all the unique back ends and allows you to locate them.

It's a drop-in form, just import it and call DoCmd.OpenForm "dlgReconnect" with the acDialog switch in your startup code. It does all the rest.
 

Users who are viewing this thread

Top Bottom