open one db form another and pass variable to it

Techworks

Registered User.
Local time
Today, 20:23
Joined
Feb 2, 2014
Messages
12
I have a button on my main db, that opens a second db using hte following code:
----------------------
'Dealing with external objects, use inline error trapping
On Error Resume Next
Dim appAccess As Object

Dim db As Database
Dim strAppPathName As String
Dim strAppName As String
Dim strTimesheetPathName As String

'get pathname of currendb
Set db = CurrentDb
strAppPathName = db.Name 'full path
strAppName = Dir(strAppPathName) 'just app name
Debug.Print strAppPathName
Debug.Print strAppName

'build file pathname for timesheet.mdb
strTimesheetPathName = Left(strAppPathName, Len(strAppPathName) - Len(strAppName))
Debug.Print strTimesheetPathName
strTimesheetPathName = strTimesheetPathName & "ProjectTime.accdb /cmd username"
Debug.Print strTimesheetPathName

'Grab the database if open

Set appAccess = GetObject(strTimesheetPathName)
If Err.Number <> 0 Then
'The database was not open so open it
Set appAccess = CreateObject("Access.Application")
Err.Clear
End If

'Make Access visible
If Err.Number <> 0 Then
MsgBox "There was an error"
Set appAccess = Nothing
Else
appAccess.Visible = True
'nb see Declare Function SetFocusAPI Lib "user32" Alias "SetForegroundWindow" (ByVal hwnd As Long) As Long in General Utilites
SetFocusAPI appAccess.Application.hWndAccessApp
End If

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

This code works great to open the other db, and handles wheter the other db is already open or not, but I cant seem to pass the variable to the other db using the startup switch /cmd.

I suspect if I used the shell method it would pass the cmd , but I havnt found any way to test if the db is already open with the shell method.

Ho can I pass a variable to the other db when opening it using VBA

Thanks

Grant
 
I think you can create a database custom property...

.. I'm not sure .... I'll have a look through my books and get back to you.
 
Open the other databse and pass the value to it using automation.
 
Galaxiom,

I've been scouring the web looking for exactly what you're talking about here but I haven't been successful. I consider myself a very experience VBA programmer with Access but I can't seem to get this to work.

Do you have an example or a link that could refer me to the automation you're speaking of?

Specifically what I'm trying to do is reduce the amount of time it takes to jump from one database to another by eliminating the need to initialize an ADO connection object that is opened at run time and left open during the duration of the session. I would like to pass the ADO connection variable that was estabilished in database 1 to the same variable in database 2.

Would the automation work in this situation?

Thanks!!
 
Example of running a function in the db opened from original db

Code:
Set appAccess = GetObject(strTimesheetPathName)
myStatus = appAccess.Run("NameOfPublicFunctionInThatDb",Variable1, Variable2)
As to your connection trick I have doubts - because these are two different processes ...

Edit: On reflection, what would the point be? If you need that connection many times then keep it open in that other db. If you only need it once, then the overhead of opening it is likely to be insignificant.
 
Last edited:
What I've noticed is that every time a connection string is opened to my Oracle server (Oracle 11), it takes a few seconds to establish that connection. When you constantly open recordsets in code it just takes too many hits and causes performance issues.

I've noticed that when the connection is left open but each recordset is closed and set to nothing, the connection allows the recordset to open noticably faster.

So my thought process was since my program consists of 10 or so smaller Access databases and the user's jump from one to another I would like to pass the connection object established in the first database to the second.
 
Users jump ? I see ... DB hopscotch :D Perhaps the jumping should be reduced ...
 
Talk to my customer.....he would rather have each one of these apps as a seperate MDE and allow us (the developers) the ability to work on separate parts of the database at the same time without overlap and possiblity of overwrite.

So I take it this probably has never been done. Probably not needed when you don't have a customer like mine. LOL
 
What has never been done? Pass the thing in the call as I showed and see what it does - that shouldn't take long. I doubt that anything will come out of it, but it might not care about my doubts :D
(BTW: Your customer is a masochist!)
 
lol, i'll give it a try when I have a few minutes Thanks for your input. :D
 
I didn't realise you were wanting to pass an object variable between databases.

I don't think that can be done directly. It is a long shot but you may be able to pass a pointer to the object. I suspect Windows might object severely to one application using another's memory.

ChrisO has demonstrated passing pointers within a database. Would be interesting if it could be done across to another database.

http://www.access-programmers.co.uk/forums/showthread.php?t=225415
 

Users who are viewing this thread

Back
Top Bottom