access vba to open another database in access

[DevAccess]

Hello,

Using VBA in access I want to open another database, as of now I am currently referring below way to open the current database, what would be best way to open another database in local drive.

Dim MyDB As dao.Database
Set MyDB = CurrentDb()

 

[arnelgp]

there are many ways.
one way is to have a linked table.

 

[DevAccess]

Hi thanks for reply, I just want to connect the other access db from local, acutally I want to seperate Front end with backend front end would have just forms where it will fetch the data from backend.

So please tell me which would be best condition using VBA I just want to open it to take reference of master table from it. and query it.



so as of now I need it using VBA only.

 

[arnelgp]

use linked table is the best for FE/BE.

other option i will give is using vba :

Dim rs As Dao.Recordset
set rs = "select * from tableInOtherDBName In 'C:\folder\OtherDBName.accdb'"

 

[DevAccess]

Here is the code arnelgp:

Set qd = MyDB.QueryDefs("qry_MasterRecords")
sSQL = qd.SQL
Set qd = Nothing
Set qd = MyDB.QueryDefs("qry_MasterRecordsReports")
qd.SQL = Replace(sSQL, ";", "") & " " & strWhere
Set rs = MyDB.QueryDefs("qry_MasterRecordsReports").OpenRecordset(dbOpenDynaset)
' MsgBox rs.RecordCount


Here MyDB is referred to current db, instead of it I would like to refer some other access db in my local c drive :

Set MyDB = CurrentDb()


it is just not master table I would want to take reference of the all the things from other database instead of current database.

 

[sxschech]

Would this link help?

http://stackoverflow.com/questions/30868626/editing-a-query-in-another-db