Run Access Function from Excel without opening database

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
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
 
What does the function in Access actually do?

If you want to insert data into Access from Excel without opening the database you should look at using ADO.
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
The function in Access triggers another macro that updates records into a table from data imported from Excel.

Apart from testing feedback, the project is complete, I left the code as was with it opening the database
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top