How to use Access DB through Excel VBA macro

[j6lemonade]

I'm not very familiar with Access and I'm learning vba in Excel.
Here's what I want to do:

Copy a column of text in Excel
Open an access DB and replace existing items in table with copied data
Open a query and copy and paste the results back into Excel
I'm doing this through an ActiveX macro activated with a button click.

I'm using office 07 but the DB is in 02-03 file format, if this makes a difference. My code stops running at the line: .DoCmd.RunCommand acCmdSelectAll and from examples I've seen online I thought this would work. Does anyone know what I'm doing wrong?

Also, when I "delete" the table items manually, I get a message asking if I want to delete XX number of records, and I select "yes". I don't know if I need extra code to bypass/affirm this prompt?

Thanks for the help.

Here's my code:

ActiveSheet.Range("A1:A" & ActiveCell.Row).Copy
ActiveSheet.Range("A1").Select

' This section retrieves the Vendor Data from the Database
Dim FilePath As String
Dim AccDatabase As Object
Dim counter As Integer
FilePath = "C:\Personal\Macros\PartSupplier.mdb"
Set AccDatabase = CreateObject("Access.Application")
With AccDatabase
.Visible = True 'Will change to False when macro works
.OpenCurrentDatabase FilePath
.DoCmd.OpenTable "tblFindParts"
.DoCmd.SetWarnings False
.DoCmd.RunCommand acCmdSelectAll
.DoCmd.RunCommand acCmdDelete
.DoCmd.RunCommand acCmdPaste

' Other code goess here to open query and copy and paste data

End With
Set AccDatabase = Nothing

 

[Trevor G]

Welcome to the forum,

I have simulated your code and adapted it to do a docmd.transferspreadsheet option and it works well as long as you are ok with replacing the table. I have also set the reference to use Access in the Excel Workbook. Under the Tools menu and reference and look for Microsoft Access version number.object library

Sub getmyShorts()
ActiveSheet.Range("A1:A" & ActiveCell.Row).Copy
ActiveSheet.Range("A1").Select
' This section retrieves the Vendor Data from the Database
Dim FilePath As String
Dim AccDatabase As Object
Dim counter As Integer
FilePath = "C:\Users\Trevor G\Documents\new search 25 04 2010.accdb"
Set AccDatabase = CreateObject("Access.Application")
With AccDatabase
.Visible = True 'Will change to False when macro works
.OpenCurrentDatabase FilePath
DoCmd.TransferSpreadsheet acImport, 10, "tblTest", "GetMySpreadsheet.xls", True, ""
End With
End Sub

 

[j6lemonade]

Thanks for the response. I tried to use the transferspreadsheet function as you suggested but I was unsuccessful. Do you have any idea why the .RunCommand lines aren't working?

 

[Trevor G]

Can you go into the database and create a MACRO, if so then you can add the Action to TransferSpreadsheet then look to import the address line, you can then save the MACRO.

Create a form and in design view drag the MACRO into the form. Then look to convert the MACRO to VBA, either through the Tools Menu and Macro or under the Database Tab if using Access 2007

Once converted you can look at the code line and see what it says and then add the code to your Excel VBA.

 

[Trevor G]

Thanks for the response. I tried to use the transferspreadsheet function as you suggested but I was unsuccessful. Do you have any idea why the .RunCommand lines aren't working?

Just re read your thread, with the transferspreadsheet you don't need the runcommand lines at all.

 

[j6lemonade]

Hi, I figured out how to do it using SendKeys. Thanks for your help.

With AccDatabase
.Visible = True '//Needs to be visible to use SendKey commands
.OpenCurrentDatabase FilePath
.DoCmd.OpenTable "tblFindParts"
SendKeys "^a", True '//Selects all existing records
SendKeys "{DEL}", True '//Deletes all existing records
SendKeys "^v", True '//Pastes items on clipboard
.DoCmd.OpenQuery "qrySupplierName"
DoEvents '//Waits for query to populate results
SendKeys "^a", True '//Selects all existing records
SendKeys "^c", True '//Selects all query results
.DoCmd.Quit
End With
Set AccDatabase = Nothing
Sheets("VENDNAME").Activate
ActiveSheet.Range("A1").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False