Getting a Handle on Current Database (1 Viewer)

[martinjward]

I have a process that will information into a recordset, to open the recordset naturally I need to get a handle on the current database.

I an attempting to create a sub that does nothing but get an handle on the current database so that if I need to make changes (i.e. split the database) I only need to change the code in a single place.

When I call the ConnectDB sub from my main routine it manages to connect to the database but when I return to the main routine the CUR_DB is empty as if it was never set.


Main Routine...

Sub LogEntry()
 
Dim CUR_DB As Database
 
Call ConnectDB (CUR_DB)
 
<<Other Code to go in here>>
 
End Sub

ConnectDB Routine...

Public Sub CurrentDatabase(DB_CUR As Database)
 
Dim WS_DBCUR As Workspace
Dim STR_DBCUR As String
 
STR_DBCUR = CurrentProject.FullName
 
Set WS_DBCUR = CreateWorkspace("", "admin", "", dbUseJet)
Set DB_CUR = WS_DBCUR.OpenDatabase(STR_DBCUR, True)
 
End Sub

Can someone please advise why I can not pass the database from the second sub back to the first please

Thanks in advanced

 

[DCrake]

'If you want to connect to the current db you may be better off using DAO

Dim Rs As DAO.Recordset
Set Rs = CurrentDB.Openrecordset(YourTable)

 

[martinjward]

Thank you for your suggestion, how does this work when I have a split database?

 

[Galaxiom]

Linked tables act as though they are in the front end database.

Edit: Ah. I see you want to edit the tables from the front end which ia why you want this solution.

Check out Bob Larson's Back End Updater as an alternative.

 

[Galaxiom]

As for your original question, the problem is your Workspace and Database variables need to be declared in the declarations section of the module to be available to all subs in the entire module. Although your sub is public the variables within it are still private.

If needed in other modules then declare them as Public in a Standard Module.

 

[LPurvis]

Well, you are already using DAO, but you are indeed trying to open a connection to the currently running MDB/ACCDB?
As mentioned - splitting in Access massively often involves maintaining linked table to the back end (there's almost no reason not to). Those links facilitate data access very much as if the tables were local (though not quite).

It doesn't seem particularly that you are wanting to edit the schema of the tables in the BE. If you were that's simple enough to grab the BE path, once you have linked tables.
(Have a look at AddTableField here as it does essentially that.)

The fact that you're opening a database pointing at
CurrentProject.FullName
enforces all of this.
That will be identical to CurrentDb (assuming sharing options even allow you to open it again.)

The code you show seems to be calling a proc called ConnectDB but lists CurrentDatabase as the actual example.
Apart from that - passing the db object to the opening proc is fine and feasible. Just not really productive. ;-)

Cheers