Run Access Function from Excel without opening database

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,314
Hi,

I have the following working code in Excel that inserts a record into an Access database table.

Can it be re-written so that there is no open/close of the database application?

Rich (BB code):
Sub New_Job_DB_Test()


    Dim r   As Range: Set r = wAdd_Job.Range("New_Job_Record")
    
    With CreateObject("Access.Application")
        .OpenCurrentDatabase Database_Params(3)
        .Run("Return_Job_ID", r.Cells(1, 1).Value, wAdmin.Range("Client_ID").Value, r.Cells(1, 3).Value, r.Cells(1, 4).Value, r.Cells(1, 5).Value, r.Cells(1, 6).Value)
        .CloseCurrentDatabase
        .Quit
    End With


    Set r = Nothing
    
End Sub
TIA,
Jack
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,742
Is there a reason that in Access you don't simply link to the spreadsheet? If you're bent on doing it the current way, then I'd say no, you cannot update a table without opening the file any more than you could update a spreadsheet without opening Excel. I would have thought that your method would make the database visible, but in testing, it doesn't seem to be. I find that odd, and I would have suggested it otherwise. Also, did you rule out TransferSpreadsheet function for this?
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
470
You can connect to your database, query it and even add a record without opening the access application - infact you can do it without having access installed on that machine.

However, you cannot access the Function without opening it. Could an option be to replicate the function in Excel and the final record just be added to the database?
 
Last edited:

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,314
Hi @Micron and @stumac, thanks for your replies. Without going into detail for reasons for why and how (too lengthy), your replies help make sense of why I need to open database in this instance.

The function it runs is saved within Access VBA and requires input variables, which ties to: "you cannot update a table without opening the file any more than you could update a spreadsheet without opening Excel

I have other code which calls a query in Access and returns data to Excel without opening the database, but these are queries, as opposed to functions as UDFs.

Still very very new with Access, not used or heard of transfer to spreadsheet - thanks for the insight.
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
470
If the the function residing in Access is dependent on other Access objects then that is probably the best way to go.

I take it you are giving each user a local copy of the front end and this is what the Excel macro is opening? If not then you may run into problems if 2 people are running this at the same time. Also if there is any issue and the code exits before the database is closed.

If you are doing that then one question would be can you cut out Excel? Access forms are far easier to use and offer a lot more functionality.
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,314
The client only wants Excel as front-end, can't cut out Excel unfortunately.

Each user has a template file which contains all necessary macros that makes calls to database, if those calls cause conflicts when >2 users use at same time, probably have to replace Access query into Excel and make a call to closed database (like some of other code I'm using).
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,742
P.S. - I like JD on ice too, if I get your signature. Especially the 100 version.:)
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,314
Haha well spotted!

I am actually now reading about making a pivot table in Excel conenct/refresh to an Access query.

If I needed to make data changes to the data pulled via this connection into a pivot table, how would I do it? More specifically, one column of data will contain TRUE/FALSE values which the User needs to update values and write back to Access.

Do I need to copy the pivot table to a normal sheet, then convert the data into a defined table form?

Similar to code in query in initial post #1 , I can then write this defined table back to the database.

Edit: This link suggests can't write back :(
https://superuser.com/questions/1063494/how-do-you-make-a-read-write-data-connection-between-two-excel-worksheets-i-am
 
Last edited:

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,742
This isn't my area of expertise. Access has a crosstab query which emulates a pivot table to a minor degree but probably not what you need. If you're working on the Excel side and modifying data you probably should be linking the sheet to Access, same as you would a table. Have to say I've never linked a sheet where the data was represented as a pivot table and would be surprised if you can link the table itself, seeing as how the pt is based on data in that sheet (or another sheet) and is only a representation of it. Thus, you'd link the spreadsheet with the data. If that was the same sheet as the pt, then I don't know what would happen. I suppose the edits you refer to are being done in the underlying sheet data, so have to think that linking the sheet as an Access table ought to be a solution.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
For basic things like adding data, editing/updating data or deleting data one doesn't need to open Access.

My reason for writing is to mention that for adding data or editing/updating data one doesn't need to open Excel either.
Delete queries can't be done.

Cross tab queries are simple in Excel too, btw. Basic form is,

TRANSFORM function(summarised field)
SELECT row fields
FROM table
GROUP BY row fields
PIVOT columnfield
 

Forum statistics

Threads
1,077,895
Messages
5,337,054
Members
399,120
Latest member
Sravankumar

Some videos you may like

This Week's Hot Topics

Top