473,324 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

How to use Access DB through Excel VBA macro?

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
2 1921
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
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

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

Similar topics

2
by: Mrs Howl | last post by:
I don't know if there's even a way to do what I want. I click on a button in an Access form, and it opens an instance of Excel, and opens a workbook and runs a macro that's in it. So far, fine,...
0
by: Alex | last post by:
i have a module in Access which opens an existing Excel file and envokes a macro within the Excel file to draw graphs. now i am trying to convert the Excel macro to an Access one so that the...
6
by: geronimo_me | last post by:
Hi, I am trying to run an Excel macro from an Access module, however when I run the code the macro runs but then I get an error in Access. The error is: Run-time error "440", Automation error. ...
2
by: cr113 | last post by:
I just upgraded from Office 2000 to Office 2003. My VB.NET Excel macro calls don't work any longer. Here is how I make my Excel macro call from VB.NET: Dim objExcel as Excel.Application ...
1
by: cr113 | last post by:
We've upgraded from Office 2000 to Office 2003 and now my excel macro calls are messed up. There's about a 15 second delay from the time I make the call in VB.NET to the time the excel macro is...
1
by: John Overton | last post by:
I have a need to run an Excel Macro from inside Access. Is this possible with Access 2003 to Excel 2003?
1
by: jdoverton | last post by:
Hey, I routinely import an Excel file, operate on several columns and export it as a separate file. I need to be able to run an Excel Macro from my Access VBA. Any Suggestions?/ Thanks...
2
by: =?Utf-8?B?Vmlua2k=?= | last post by:
Hello Everyone, I exported a gridview to excel spreadsheet. I want to adjust the column width of the excel spreadsheet. Is it possible to create Excel macro from C# to adjust the column width...
3
by: redbenn | last post by:
Hello, I am trying to create an excel macro, that when clicked... a specific cell will match a record in my database, and update certain fields in this record. The Excel file will have a cell...
1
MitchR
by: MitchR | last post by:
Good Morning Folks; I have a question that is pretty far fetched but here goes nothing... I am looking to find a way to insert a macro into an Excel command button located in an Access VBA...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.