Need help in accessing second table data

[ashishprem]

Hi,
I have forms and macro written in a file say MDB1. Is there any way to access the data of table present in file MDB2.
To access the data from current database I am using the code as

Dim lrs As Recordset
Dim lrs1 As Recordset
Dim db As Database
Dim i As Integer
Set db = CurrentDb()
Set lrs = db.OpenRecordset("SELECT * from table1");

Is there any way that I just define the database name of second access filename path and access its data by using macro.

Any suggestions?

Thanks guys
Ashish

 

[namliam]

You can create a linked table to access data in external databases.
Doesnt much mater if that is an Access or Oracle or SQL or any other Database.

Right click your tables screen (the white part if it)
Select linked tables.
Find what ever you are looking for

Now once it is linked you can use it much the same as any "normal" table in your DB.

 

[ashishprem]

Hi,

Is there any way to access without using linked tables. I need to define the MDB path in macro and access the table data using macro, not using linked tables.


Ashish

 

[namliam]

Sure there is.... but that is the hardway around the problem.
So either
1) it is your own problem and you fix it the easy way
2) It is a school project, and you have to figure it out yourself
3) You have some odd kind of request somehow where somebody is telling you to do something odd... you have to go back and tell them they are crazy

 

[ashishprem]

Hi,
might be I am not able to relate my requirement with your solution. What my requirement is :
I have two tables in MDB1. And I am using the table data in my forms and macros present in MDB1. Now I have a form present in MDB1. In this form I am giving a file path say MDB2 file path. Once we specify the file path in the form it copies the data from 2 tables present in MDB2 and updates in 2 tables present in MDB1. I cannot link it because all the time MDB2 path is changing. So i thought of making a form where I can specify a file path and copy the table data and paste in MDB1.
Hope I am did not confuse much.

Any suggestion
Ashish

 

[namliam]

In an Append or "create table" query you can specify which DB you want it created it.

A linked table is not fixed to one DB tho, you can change the linked source.
Currentdb.tabledefs("TableName").Connect
Contains your connection details for the linked tables. Changing it to suite your needs....
then make sure to "activate" it by doing a refresh
Currentdb.tabledefs("TableName").RefreshLink

Good luck with either way you wish to go !

 

[Mike375]

If you are moving DB2 around you could make DB3 which is static. The tables in DB2 could be put in DB3 and both DB2 and DB1 link to them.

If you link A to B then you can move A around. The linking does not reference the postion of the DB where you are doing the linking from.

 

[ashishprem]

i tried using linking tables and it worked too. But it doesn't solve my problem fully. My Development MDB file(say MDB1) contains 10 tables. I need to copy the data from production MDB files(Say MDB2) often. But the thing is I need to only 5 tables data from MDB2 to MDB1. So the option of copying and renaming MDB2 as MDB1 will not work. Moreover, for 5 table if i link to the new tables then I am having multiple links to the front end file which might be an issue while transferring it from one place to other. So i need to go for some other option.. hope I am clear in my saying.. any suggestion??

 

[Mike375]

i tried using linking tables and it worked too. But it doesn't solve my problem fully. My Development MDB file(say MDB1) contains 10 tables.

So MDB1 is where you make things in Access.

I need to copy the data from production MDB files(Say MDB2) often.

MDB2 is the data base that is actually being used for data base purposes.

But the thing is I need to only 5 tables data from MDB2 to MDB1. So the option of copying and renaming MDB2 as MDB1 will not work.

Is MDB2 the data base that is being transferred from one to place to another. Is the "transferring" as in a laptop that is taken out (off the system) or is transferring MDB2 mean moving it do different folders or other computers on the network.

If "transferring" means moving out as in a laptop then of course the linking will fail.

These 5 tables you are talking about in MDB2......do they have the same name as 5 of the tables in MDB1?

If you are going to automate this then one of the data bases needs to be in the one position. Import being done is like linking in that the DB you import into can be in different folders, computers, as long as there is a connection. In other words the reference or pathway is to the data base you import from or link to.

Note: If linking won't work because one of the data bases goes outdoors on a laptop then import won't work.

I am not really clear on what you are doing but as long as one computer is static you can automate Import and that might need to be accompanied by Rename, which can be automated.

You can also easily with a "click" have the data base copied to other computers on the network.

But if you need up to the date data from the other computer linking is the only way, unless of course you automate an import so it runs in 30 seconds

Also remember that if MDB2 is moved off the system then you can't have updates at your end until the MDB2 returns. If the computer is disconnected and has linked tables the links re establish on reconnection.

The other thing to consider if MDB1 and MDB2 are being moved around a network or moving to different folders is as I suggested earlier, that is, have a third data base that is in a static position and use that as a connecter.

 

[ashishprem]

Thanks for deep anlaysis. My requirement is something like this.
I have a table DEV_FE.MDB which contains the forms and macros. This contains table linked to file DEV_BE.mdb which contains the tables and data. This is working fine with me.

Now very often I need to copy these two files from my PC to some other PC in different path. So i need to update the table links.
If my DEV_FE.MDB is palced in C:\TEST folder then always the link table path should be C:\TEST\DEV_BE.MDB. I mean the linked tables files will be in same folder only but the root path will change. Any suggestion to make the link tables get updated automatically but seeking the path of DEV_FE.MDB path.

Let me know if I am unclear in the explaination.

Thanks in advance.

Ashish

 

[namliam]

I believe I have exactly that for one of my customers somewhere.... but cannot find it for the moment.

Have a look at this sample and try and change it...

Sub TableDefX()

    Dim dbsNorthwind As Database
    Dim tdfNew As TableDef
    Dim tdfLoop As TableDef
    Dim prpLoop As Property

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' Create new TableDef object, append Field objects 
    ' to its Fields collection, and append TableDef 
    ' object to the TableDefs collection of the 
    ' Database object.
    Set tdfNew = dbsNorthwind.CreateTableDef("NewTableDef")
    tdfNew.Fields.Append tdfNew.CreateField("Date", dbDate)
    dbsNorthwind.TableDefs.Append tdfNew

    With dbsNorthwind
        Debug.Print .TableDefs.Count & _
            " TableDefs in " & .Name

        ' Enumerate TableDefs collection.
        For Each tdfLoop In .TableDefs
            Debug.Print "  " & tdfLoop.Name
        Next tdfLoop

        With tdfNew
            Debug.Print "Properties of " & .Name

            ' Enumerate Properties collection of new
            ' TableDef object, only printing properties
            ' with non-empty values.
            For Each prpLoop In .Properties
                Debug.Print "  " & prpLoop.Name & " - " & _
                    IIf(prpLoop = "", "[empty]", prpLoop)
            Next prpLoop

        End With

        ' Delete new TableDef since this is a 
        ' demonstration.
        .TableDefs.Delete tdfNew.Name
        .Close
    End With

End Sub

I pasted this from the Access help, so more info can be found there.
The connection to the backend is one of the "properties" of the linked table.
Currentdb.Name contains the FULL path and name of the database that is currently open.

Using above coding, changing that and utilizing the Currentdb.name feature you should be able to do what you want.

 

[ashishprem]

Hi,
I got the code for table linking work and that solves my problem well. Thank you so much. One more question. I have a function say "relink" which takes care of table relinking. I have put this function in form load. Is there any way that I can call this function when the database gets loaded/opened?

Ashish

 

[namliam]

The way I solved that is to create a small form which says something like:
"Relinking tables please wait a few seconds"
This form then closes when done or nothing to do, opening the "main menu".