Open an Access DB from Excel

[crossles]

Hi there, I am trying to open an access database from excel by clicking a button. I have assigned the following script and modified it for my own use as according to the microsoft kb.

Private Sub CommandButton2_Click()
'Opens Microsoft Access and the file nwind.mdb
Shell ("c:\Program Files\Microsoft Office\OFFICE11\MSACCESS.exe T:\TSD - UK\Projects\Steve's Projects\T3\FCRs.mdb")
'Initiates a DDE channel to Microsoft Access
Chan = DDEInitiate("MSACCESS", "system")
'Activates Microsoft Access
Application.ActivateMicrosoftApp xlMicrosoftAccess
'Runs the macro "Sample AutoExec" from the NWIND.MDB file
Application.DDEExecute Chan, "ImportData"
'Terminates the DDE channel
Application.DDETerminate Chan

End Sub

The problem I have is that although this does not debug, it does not do what I want it to.

I want this to launch Access, then launch the file FCRs.mdb, then launch the macro ImportData.

What it actually IS doing is the following:

ERROR 1:

Remote Data not accessible.
To access this data Excel needs to start another application. Some legitimate applications on your computer could be used maliciously to spread viruses or damage your computer. Only click yes id you trust the source of this workbook and you want to let the workbook start the application.
Start application 'MSACCESS.EXE'?


Even though access has by now already started anyway, I then click yes.

ERROR 2:

Microsoft Office Access can't find the macro 'DataInput.'
The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under.


I then click ok, knowing the macro exists, but soon realising the application has opened, and the macro is trying to open, but the file isnt opening at all.


ERROR 3:

The command line you used to start Microsoft Office Access contains an option that Microsoft Office Access doesn't recognize.
Exit and restart Microsoft Office Access using valid command line options.


I click ok, and get ERROR 3 again.... and again.


ERROR 4:

Microsoft Office Access cant find the database file 'T:\TSD.mdb'
Make sure you enterred the correct path and filename.



So now at the end of the cascading messages, i eventually find what seems to be the problem. The macro couldnt be openned because the file wasnt open, because it was looking for T:\TSD.mdb, when it is supposed to be looking for T:\TSD - UK\Projects\Steve's Projects\T3\FCRs.mdb.

My big guess is that this has something to do with spaces in names, because if i change it to T:\TSD-UK\Projects\Steve'sProjects\T3\FCRs.mdb, i get the same problem, but with the full path name, so how do i resolve this problem?

I have the following refferences attached:

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Access 11.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library

Please help

 

[Andrew Fergus]

Hi

It sounds to me like the issue is with the 'ImportData' macro. Can you run the macro 'ImportData' from within the FCRS database, without using Excel?

Andrew

 

[crossles]

yea thats not a problem. The thing is, the importdata macro isn't loading because the excel sub isnt even getting as far as opening the database.

 

[tusharm]

From what I can tell, you should replace "ImportData" with a Query in the Access database and then use ADO to access the query.

You can search Google for examples of how to use ADO from Excel-based VBA to access Access. You will find any number of explanations and code samples.

You can even parameterize the Query and treat it as a (very primitive) stored procedure.

Hi there, I am trying to open an access database from excel by clicking a button. I have assigned the following script and modified it for my own use as according to the microsoft kb.

Private Sub CommandButton2_Click()
'Opens Microsoft Access and the file nwind.mdb
Shell ("c:\Program Files\Microsoft Office\OFFICE11\MSACCESS.exe T:\TSD - UK\Projects\Steve's Projects\T3\FCRs.mdb")
'Initiates a DDE channel to Microsoft Access
Chan = DDEInitiate("MSACCESS", "system")
'Activates Microsoft Access
Application.ActivateMicrosoftApp xlMicrosoftAccess
'Runs the macro "Sample AutoExec" from the NWIND.MDB file
Application.DDEExecute Chan, "ImportData"
'Terminates the DDE channel
Application.DDETerminate Chan

End Sub

The problem I have is that although this does not debug, it does not do what I want it to.

I want this to launch Access, then launch the file FCRs.mdb, then launch the macro ImportData.

What it actually IS doing is the following:

ERROR 1:

Remote Data not accessible.
To access this data Excel needs to start another application. Some legitimate applications on your computer could be used maliciously to spread viruses or damage your computer. Only click yes id you trust the source of this workbook and you want to let the workbook start the application.
Start application 'MSACCESS.EXE'?


Even though access has by now already started anyway, I then click yes.

ERROR 2:

Microsoft Office Access can't find the macro 'DataInput.'
The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under.


I then click ok, knowing the macro exists, but soon realising the application has opened, and the macro is trying to open, but the file isnt opening at all.


ERROR 3:

The command line you used to start Microsoft Office Access contains an option that Microsoft Office Access doesn't recognize.
Exit and restart Microsoft Office Access using valid command line options.


I click ok, and get ERROR 3 again.... and again.


ERROR 4:

Microsoft Office Access cant find the database file 'T:\TSD.mdb'
Make sure you enterred the correct path and filename.



So now at the end of the cascading messages, i eventually find what seems to be the problem. The macro couldnt be openned because the file wasnt open, because it was looking for T:\TSD.mdb, when it is supposed to be looking for T:\TSD - UK\Projects\Steve's Projects\T3\FCRs.mdb.

My big guess is that this has something to do with spaces in names, because if i change it to T:\TSD-UK\Projects\Steve'sProjects\T3\FCRs.mdb, i get the same problem, but with the full path name, so how do i resolve this problem?

I have the following refferences attached:

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Access 11.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library

Please help

 

[Smitty]

Why not use Excel's native Data-->Get External Data-->New Database Query-->Follow the Wizard from there.

HTH,

Smitty

 

[tusharm]

He, he, he! Why not indeed? After recommending it so many times how could I forget it?

Oh, I know why. I've been immersed in ADO for the past few hours as I created and documented a way to seemingly make a value and a formula co-exist in a single cell (http://www.dailydoseofexcel.com/arc...ng-a-number-and-a-formula-co-exist-in-a-cell/).

But, yes, using MS Query is probably the first place to look.

Why not use Excel's native Data-->Get External Data-->New Database Query-->Follow the Wizard from there.

HTH,

Smitty

 

[Smitty]

http://www.dailydoseofexcel.com/arc...ng-a-number-and-a-formula-co-exist-in-a-cell/

Very interesting. Thanks.

Smitty

 

[crossles]

If i explain a little more about what i am trying to do:

I have a big project which consists of multiple excel books and access databases. With this particular link, the excel sheet is limited to 1600 records. each dump contains 1600 records, and these are then sent from excel to access. the ImportData macro in access picks up the data from Excel and imports it into a controller database. This data is then manipulated in other excel books in many ways.

By using the get external data function in excel, this will not help me shift data from excel to access. What I have is a form which looks up a raw data csv file, and the process then shifts data into the book, and then launches access.

What i am trying to do, is the sub then runs a macro from that access db, and at the end of the access macro, access is forced to close. The data is then removed from the excel database, and the user is prompted that everything has been exported fine.

This is not happening though, as the access db is not loading.

 

[Andrew Fergus]

Hi

To open a database from Excel and run a macro, try this code:

Sub MyButton_Click()

Dim LPath As String, oApp As Object

LPath = "C:\MyPath\MyDatabase.mdb"
Set oApp = CreateObject("Access.Application")
oApp.Visible = True
oApp.OpenCurrentDatabase LPath
oApp.DoCmd.RunMacro "MyMacro"
oApp.DoCmd.Quit acQuitSaveAll
Set oApp = Nothing

End Sub

This method works for me using Excel and Access 2003. Make sure you use your actual path, database and macro names where you see My... within the code.

Andrew

 

[NateO]

Hello,

Another thought...

I don't think I'd be running an Access Macro for the sake of pushing data from Excel to Access.

I think I'd be pushing the Excel Range's values to an Array, and then push the Array into an Access Recordset. E.g.,

http://www.mrexcel.com/board2/viewtopic.php?p=1232569#1232569