VBA to Paste data into Access table

[ARW17]

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!

 

[ranman256]

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.

 

[ARW17]

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!

 

[xenou]

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.

 

[ARW17]

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?

 

[xenou]

Is the other database an Access database?

 

[ARW17]

No. The other database is what we call our mainframe. It is not an Access database.

 

[Joe4]

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.