By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,143 Members | 708 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,143 IT Pros & Developers. It's quick & easy.

How to use Access DB through Excel VBA macro?

P: 4
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. Could you tell me what I'm doing wrong? Thanks.

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

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

End With
Set AccDatabase = Nothing
May 25 '10 #1
Share this Question
Share on Google+
2 Replies

P: 4
I forgot something:

After the .DoCmd.RunCommand acCmdDelete line I would paste in the contents of the cells I originally copied. Doing the "delete" 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?

Any help will be appreciated. Thanks.
May 26 '10 #2

P: 4
FYI, I figured out how to do it using SendKeys:

With AccDatabase
.Visible = True '//Needs to be visible for SendKey
.OpenCurrentDatabase FilePath
.DoCmd.OpenTable "tblFindParts"
SendKeys "^a", True '//Selects all existing records
SendKeys "{DEL}", True '//Deletes 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
Jun 1 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.