Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Connecting to Access from Excel VBA
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Connecting to Access from Excel VBA
· Urgent Priority · Version 2003
Reply Mon 12 Sep 2011, 12:32Delegate Sakeb said...
Sakeb has attended:
Excel VBA Intro Intermediate course
Connecting to Access from Excel VBA
Morning All
I am currently trying to develop a piece of code which will connect to an MS Access database and then kick off a Query on the table
I am using the following code:
Code:
Public strDB As String
Public A As Object
Public Function runaccessmacro()
Application.ScreenUpdating = False
Set A = CreateObject("Access.Application")
strDB = "S:\MI_Portal\TMNT\TMNT (TESTING).mdb"
A.OpenCurrentDatabase strDB
A.Visible = False
A.DoCmd.RunMacro "mcrUpdateNumberStatus"
A.Quit
Set A = Nothing
Application.ScreenUpdating = True
End Function
Sub run_access_macro()
Call runaccessmacro
End Sub
But this does not work and am presented with error message '7866', which suggests the table can not be found or is locked by another user
I have also tried to following code, but get the same error message:
Code:
Global oApp As Object
Sub OpenAccess()
Dim LPath As String
Dim LCategoryID As Long
'Path to Access database
LPath = "\\RLBYCCMPFS06\swap\MI_Portal\TMNT\TMNT (TESTING).mdb"
'Open Access and make visible
Set oApp = CreateObject("Access.Application.11")
oApp.Visible = True
'Open Access database as defined by LPath variable
oApp.OpenCurrentDatabase LPath, False
'Open form called Categories filtering by CategoryID
oApp.Visible = False
oApp.DoCmd.RunMacro "mcrUpdateNumberStatus"
oApp.Quit
End Sub
Any suggestions??
For upcoming training course dates see: Pricing & availability
RE: Connecting to Access from Excel VBA
Hi Sakeb, thanks for your query. We'll get back to you asap on this one.
Anthony
Reply Mon 3 Oct 2011, 10:44Delegate Sakeb said...
RE: Connecting to Access from Excel VBA
Hi Guys
It's been well over two weeks since I posted this question, yet still no answer - should I look elsewhere? I thought you guys were the experts
Rgds
Sakeb
|