VBA to Paste data into Access table

ARW17

Board Regular
Joined
Oct 31, 2016
Messages
109
I need to copy data from a username/password protected "homegrown" database (that can be a little slow) and paste it into an Access Table. I wrote a macro in our "homegrown" database that I linked to `. I'm able to do the following in Access:

Sub AutoExec()
AppActivate "Session 1"
delay (1)
SendKeys ("`")
delay (1)
DoCmd.OpenTable "QueueMonitor"

As a result, I have data pending on my clipboard that I need to paste to my table "QueueMonitor" AND format it from text to columns.

Do I need to go to Excel first and then upload it to Access?

Can I use the "split function" in Access instead?

The macro in our "homegrown" database works as long as I'm already signed in. How would you deal with the macro failing if you're not logged in? I could include the credentials in my code, but we change passwords frequently and we'll be sharing who is using the db and who may be logged in on a particular day.

Thanks for all suggestions!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
you dont copy /paste data into access,
you either run TRansferspreadsheet to pull excel data into a table
or
run a query to insert data into a table from excel.
 
Upvote 0
Ok, so I need to use Excel as an intermediate step?

Can I use VBA in Access to open Excel and paste data there, change it to text to columns, then upload it to Access?

Do I need VBA code in Excel too or can I get through it all in Access?

Thanks!
 
Upvote 0
Hi,
Excel has a button to get data from Access. Shouldn't really need any macros at all.
Probably a place to start is just googling "MSAccess YouTube Import Access Data to Excel" - very likely there's videos showing you how to do this. Using coded commands for copy/paste stuff is not how you would normally take care of importing/exporting data when dealing with a database.
 
Last edited:
Upvote 0
Hi,
Excel has a button to get data from Access. Shouldn't really need any macros at all.
I need to get the data from another database (copy it) and then ultimately get it to Access. I don't know the best way to do this. I have code written for Access to run the macro in the other database and copy what I need, but then I don't have a way to get that copied info into Access. Does that make sense?
 
Upvote 0
Is the other database an Access database?
 
Upvote 0
No. The other database is what we call our mainframe. It is not an Access database.
 
Upvote 0
I have worked with data form MainFrame databases before. Typically, we export text files out of the MainFrame database, and import it into Access.
I have never tried to copy and paste data in, and wouldn't advise it.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top