Run Access Function from Excel without opening database

[JackDanIce]

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?

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]

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]

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?

 

[JackDanIce]

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]

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]

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]

P.S. - I like JD on ice too, if I get your signature. Especially the 100 version.

 

[JackDanIce]

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/106...-connection-between-two-excel-worksheets-i-am

 

[Micron]

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]

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