How to reference Database

[MUKESHY12390]

Hi All,

How to reference database which stored in different folder.

eg : [FONT=&quot] Set db = CurrentDb , how can I replace [/FONT][FONT=&quot]CurrentDb[/FONT][FONT=&quot] with database stored in path:C/mydata, name:myAccessDB[/FONT]
.

Public Sub delTbl()
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Set db = CurrentDb

   For Each tdf In db.TableDefs
         
   Next
End Sub

Regards,
Mukesh Y

 

[Trevor G]

Try something like this:

Set db ="C:\mydata\myAccessDB.mdb" 'if it is access new style database use .accdb instead of .mdb

 

[jackd]

Here is a routine that does more than you requested, but what you asked is illustrated in the code.
This code is resident in my currentdb which is
?currentdb.Name
C:\Users\mellon\Documents\JJJed_NewJune212014_Backup_May416.accdb

'---------------------------------------------------------------------------------------
' Procedure : testRockydb
' Author    : mellon
' Date      : 07/06/2016
' Purpose   :
' Working from a database, check if a certain table exists in current or second database,
' if it does then delete it, then create a table in a second(external database) and
' link it to your original database ---all in vba.
'---------------------------------------------------------------------------------------
'
Sub testRockydb()

    Dim dbFrontEnd As DAO.Database
    Dim dbsSecond As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim ind As DAO.index
    Dim tdfLinked As DAO.TableDef
    Dim gstrDatabaseName As String
    '
    ' ONE OF MY DATABASES with 2 tables
10       On Error GoTo testRockydb_Error

20    gstrDatabaseName = "c:\users\mellon\downloads\EquipInventoryOrLocation.mdb"
30    Set dbsSecond = DBEngine.Workspaces(0).OpenDatabase(gstrDatabaseName)
    '"c:\users\mellon\downloads\EquipInventoryOrLocation.mdb")

    'see if table already exists in external database
40    For Each tdf In dbsSecond.TableDefs
50      If tdf.name = "tblPODetailRemarks" Then
60          dbsSecond.TableDefs.Delete "tblPODetailRemarks"
70      End If
80    Next tdf

    'see if table already exists in current database
90    For Each tdf In CurrentDb.TableDefs
100     If tdf.name = "tblPODetailRemarks" Then
110         CurrentDb.TableDefs.Delete "tblPODetailRemarks"
120     End If
130   Next tdf

    ' Create Table in the second database
140   Set tdf = dbsSecond.CreateTableDef("tblPODetailRemarks")
150   Set fld = tdf.CreateField("fldPODetailRemarksID", dbLong)
160   fld.Attributes = dbAutoIncrField
170   tdf.fields.Append fld

180   Set ind = tdf.CreateIndex("PrimaryKey")
190   With ind
200     .fields.Append .CreateField("fldPODetailRemarksID")
210     .Unique = False
220     .Primary = True
230   End With
240   tdf.Indexes.Append ind

250   Set fld = tdf.CreateField("fldPODetailRemarks", dbText, 255)
260   tdf.fields.Append fld

270   dbsSecond.TableDefs.Append tdf
280   Set dbFrontEnd = CurrentDb
    ' Link Table


290   Set tdfLinked = dbFrontEnd.CreateTableDef("tblPODetailRemarks")
300   tdfLinked.Connect = ";Database=" & gstrDatabaseName
310   tdfLinked.SourceTableName = "tblPODetailRemarks"

320   dbFrontEnd.TableDefs.Append tdfLinked
330   dbFrontEnd.TableDefs.Refresh
340   Application.RefreshDatabaseWindow
350   MsgBox "finished"
  
testRockydb_Exit:
360      Exit Sub

testRockydb_Error:
370      MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure testRockydb of Module ModuleTesting_CanKill"
380      Resume testRockydb_Exit
End Sub

Good luck

 

[xenou]

Note: depends a little on what you are doing. simplest, for example, if you want to use a table in another database, would be to just link the table. Then that table can be used just like other tables in your database.

 

[MUKESHY12390]

I have two database DB_A (Blank table) and DB_B (table has records), both have same tables.

Situation : I wanted to import all data from DB_B to DB_A.

Just wanted to know, what would be better method.

Looping DB_A with DB_B -->INSERT INTO ?

Thanks for response.

 

[MUKESHY12390]

thanks! half my problem solved.

 

[jackd]

Using what xenou said, if you open database_A, and using the wizard external source is Access, locate database_B, and choose import then select all tables.
And all tables in database_B will be imported to database_A

 

[MUKESHY12390]

Using what xenou said, if you open database_A, and using the wizard external source is Access, locate database_B, and choose import then select all tables.
And all tables in database_B will be imported to database_A

Using your solution ( "DBEngine.Workspaces(0).OpenDatabase(gstrDatabaseName)")

....
now I can loop through other Database from one database.