Opening Another Access DB (1 Viewer)

[woodsy74]

I have a current database that will open up other databases and run macros. The current database has a form which lists out the path of the other databases and the macros that need to be run. Currently all of this works fine. However, we are finally moving over to Windows 7 & I am running into issues while testing. Apparently my queries only run properly when I open up MS Access from a desktop shortcut that IT created. If go through My Computer and open the db directly from there then my queries don't run. This is what is causing my issues with the current database.

Going through the code I see "Set appAcc = New Access.Application" as the spot when it opens up a new access and then navigates to the db location.

Can anyone tell me where "Set appAcc = New Access.Application" opens up the new access from and is there a way for me to control it? From what I understand I need to somehow make it open from C:\Program Files x86\Microsoft Office\Office15\MSACCESS.EXE and then navigate to the db location.

I don't know if I made any sense so please let me know if you have any questions or need anything else. Thanks for your time.

 

[JHB]

When you say your queries don't run, what happen - some error message or ..?
I don't know if that would change anything in your case, but you can also use the CreateObject.

Set appAcc = CreateObject("Access.Application")

 

[Solo712]

I have a current database that will open up other databases and run macros. The current database has a form which lists out the path of the other databases and the macros that need to be run. Currently all of this works fine. However, we are finally moving over to Windows 7 & I am running into issues while testing. Apparently my queries only run properly when I open up MS Access from a desktop shortcut that IT created. If go through My Computer and open the db directly from there then my queries don't run. This is what is causing my issues with the current database.

Going through the code I see "Set appAcc = New Access.Application" as the spot when it opens up a new access and then navigates to the db location.

Can anyone tell me where "Set appAcc = New Access.Application" opens up the new access from and is there a way for me to control it? From what I understand I need to somehow make it open from C:\Program Files x86\Microsoft Office\Office15\MSACCESS.EXE and then navigate to the db location.

I don't know if I made any sense so please let me know if you have any questions or need anything else. Thanks for your time.

Have you considered simply running the databases that need to be maintained from a Windows scheduler ? Seems like a much simpler way to accomplish what you are seeking to do .

Best,
Jiri

 

[woodsy74]

The queries don't run because I get ODBC Connection errors. The queries only work when we open databases through a desktop shortcut that was set up by IT. That's why I am trying to control how the new Access is created.

I don't think I am allowed to set up a Windows Scheduler. We had asked IT and they provided no solutions so someone on the team created this database for us that opens up the other databases and runs the macros.

Let me know if you have any other questions. Thank you.

 

[JHB]

Have you tried the CreateObject("Access.Application") method?

 

[Solo712]

Going through the code I see "Set appAcc = New Access.Application" as the spot when it opens up a new access and then navigates to the db location.

Can anyone tell me where "Set appAcc = New Access.Application" opens up the new access from and is there a way for me to control it? From what I understand I need to somehow make it open from C:\Program Files x86\Microsoft Office\Office15\MSACCESS.EXE and then navigate to the db location.

You don't need to specify a path to Office15 unless you are running Access from Shell. This does not work ?

Set appAcc = New Access.Application
appAcc.OpenCurrentDatabase("d:\mypath\myDB.accdb")

Best,
Jiri

 

[Trevor G]

Try something like this, change the details in coloured in Red

Sub openAnotherDB()
Dim accApp As Access.Application
Set accApp = CreateObject("Access.Application")
accApp.OpenCurrentDatabase ("c:\Access\Overtime Tracker Database.accdb")
accApp.DoCmd.OpenQuery "qryEmployeelog2"
accApp.Visible = True
End Sub

 

[BlueIshDan]

You're going to want to start it with Shell because creating a link between two access database's tends to get messy!

 

[gemma-the-husky]

I would investigate the desktop short cut

It must be running a batch file that sets some paths for the database to use, rather than just launching the dbs itself

 

[BlueIshDan]

"C:\Program Files\Microsoft Office\Office11\msaccess.exe" "C:\Program Files\Microsoft Office\Office11\samples\northwind.mdb" /X macroName


replace the addresses with the correct ones and the macroName as well.

 

[Trevor G]

What have you tried from the suggestions you have received so far?

 

[jdraw]

Why do the macros reside in different databases?

 

[Solo712]

Why do the macros reside in different databases?

He said the database that he is trying to get going is there for the single purpose of opening other databases to run macros in them (maintenance presumably). It does not make much sense to me. He says they do it this way because their IT guys would not let them run Windows Task Scheduler. It won't work except from a shortcut ! It is much more likely that whoever has devised this way of doing things is not aware that you can open the database (with the macros) from a command line via batch file. Put it in the Task Scheduler and run it whenever you want.

BTW, I have shown the technique of running macro via shell / task scheduler here

Best,
Jiri

 

[woodsy74]

I've tried using Set appAcc = CreateObject("Access.Application") but I get the same ODBC Connection error when it tries to run the macro.

I think when the code opens the new MS Access it is going to C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE. In order for the ODBC Connections to work the IT Dept forced things to open up from C:\Program Files x86\Microsoft Office\Office15\MSACCESS.EXE (no parentheses around the x86).

I may have to forget about this current db and try building something that uses things that you guys have mentioned like
"C:\Program Files\Microsoft Office\Office11\msaccess.exe" "C:\Program Files\Microsoft Office\Office11\samples\northwind.mdb" /X macroName

Thanks.