Run module on changing database names

[UnsolvedCoding]

Hey all -

I am working on a project that has some things I have never encountered before.

Basically what happens is we get Access databases handed off to us regularly that is under a different name each time. Currently the process is to select the correct Access database, add a VBA module, manually copy and paste code into the VBA module and then run the code, close the database without saving the module, open excel and run the workbook that does everything else.

What I want to do is either input the code for the module into Access via code or adapt the Excel workbook to create a module that will use an input box to open the correct database, run the module in that database and then close out. In otherwords, use Excel to open Access, run the missing Access code, close Access without saving and continue forward with the Excel portion or the process.

Any ideas?

 

[MajP]

The module can simply exist in you Excel file, no need for it in the Access database. Just have a reference to Access. Like you said, just have a file dialog to pick the database.

 

[UnsolvedCoding]

How can I find the path to Access 2007? I can't seem to find the location anywhere using any means. Is it true that the path is totally hidden?

 

[UnsolvedCoding]

Let me put this a different way, since there are a couple of issues coming up.

1 - I can't locate the path to Access on my computer. I can find the short cut but not the actual path.

2- The database isn't running the code from the Excel module.

With objAcc
.Run ("CheckAndCorrectColumns")
End With

Doesn't run the code to check if the correct column headers are in place

3 - Did I word this right?

Set objAcc = GetObject(, "Access.Application")
Set objAcc = CreateObject("Access.Application")

 

[MajP]

Can you restate that question with more detail? That could mean several things depending on from where you want to get the path. From the local Access database, from a linked table, from a file browser.
My suggestion was run the Excel file, using automation of the Access object. Use a file browser in Excel to pick the path to the db.

 

[MajP]

Disregard my last post. We posted at the same time.

 

[MajP]

Based on how I think this should be implemented, those questions make no sense to me. If the database you are getting changes and you do all the processing in Excel then your implementation makes no sense to me. So I am not that interested in answering questions using your implementation, because it seems like fixing a broken process.

Basically what happens is we get Access databases handed off to us regularly that is under a different name each time. Currently the process is to select the correct Access database, add a VBA module, manually copy and paste code into the VBA module and then run the code, close the database without saving the module, open excel and run the workbook that does everything else.

My implementation is.
1) All code in excel
2) Excel has a reference to access
3) use file browser to select the current database
4) use automation or data linking to process data in excel

 

[UnsolvedCoding]

You are correct, the process is broken and I got it because it needs to be fixed.

Some of the things I can't change - like the databases being created in a different office under names that change all the time.

You should see the SQL...

 

[MajP]

Can you implement the approach I am suggesting? Is there a reason not to implement that way? Is there a need for any code in the database? If there is need to have code in the database, why not have all code, forms and modules in the front end and link to the data in the changing database? Does the new database come with new code, forms, reports, queries or just new data?

 

[UnsolvedCoding]

The end goal is to re-write everything to run from Excel so that end users don't have to open 4 different applications.

A large portion is being re-written into SQL to speed up the mainframe search. That will be dumped to Excel when completed.

Then we have to run Access. I want to code so that Access be run and coding pulled from Excel.

The last part is re-writting the VBA portions that make the data user friend in Excel.

Time has been cut in half already but it still takes 10-15 minutes to run. The original VBA was something like 2000 lines of code for one macro.

It was really FUBAR.

 

[MajP]

Not sure if there is a question in your last response. As I said the solution is

1) Place all code in excel
2) Excel has a reference to access
3) use file browser to select the current database
4) use automation or data linking to process data in excel

Which part are you struggling to code?

 

[UnsolvedCoding]

In answer to your points -

1 - The module that holds the code is in Excel. But because I am running into problems opening access I can't test it yet. It works in Access as is.

2 - The references include the Access 12.0 object library (and I forget how to shut Access so it doesn't stay open).

3 - the user fills in a database name and the string is added from there, already working in a different program, just need to find the correct path to Access.

4 - This is the biggest issue I have at the moment. The macro that needs to run in Excel should be something like

with application
run "Sort_Rows"
end with

However I think that code runs a macro in Access instead of running the code from Excel. The other part of this is that there are several functions that were built and need to be reworded for Excel.

At this point if I can get specific coding on how to link to Access from Excel, along with how to run code in Excel that makes Access do what I want it would be helpful.

Something simple like code to show how to open Access, run a macro in Excel that gives a msgbox in access saying Hello and returns a Hello from a table.

 

[UnsolvedCoding]

Come to think of it I belive application.quit or something similar closes Access.

 

[UnsolvedCoding]

This is what I have for hooking into Access so far

Set objAcc = GetObject(, "Access.Application")

Set objAcc = CreateObject("Access.Application")
objAcc.OpenCurrentDatabase "C:\Desktop\Test_Database.mdb"
' objAcc.Visible = True

With objAcc
.Run ("CheckAndCorrectColumns")
End With