Solved Difficulty calling a macro from a different database to export a query as a text file (1 Viewer)

LouisPrefersExcel

New member
Local time
Today, 12:33
Joined
Jun 29, 2021
Messages
6
Hi Everyone,

So my issue currently is:

I would like to run a macro to call a query from a database that contains a lot of information I need for what I am doing (this separate database is owned by someone else and multiple people input data into it). I have setup a query in this separate database that extracts only the data I need (though it takes 40 seconds or so to run), I was then planning on setting up a macro to run this query and export the results as a text file so that I can import the data from the text file back into my database.

I have found some code to put into Access' VBA to export the query to .txt and name it with a pre-set file path but at the moment I can't even export the query to a text file as I am getting the message "There isn't enough memory to perform this operation. Close unneeded programs and try the operation again.". The reason I was exporting first is so that I could save the export steps and use this name in the VBA code.

I have tried closing everything and reopening Access and trying again, but no luck. The weird thing is that I can export to Excel immediately.

Is what I'm currently trying to do the best way to import data from a different database's query into my own database, or is there a better alternative? And does anybody know why I can't export my query as a text file?

Thank you all in advance.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:33
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

Have you tried linking to the tables in the external database and executing a query export locally?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:33
Joined
Jul 9, 2003
Messages
16,244
The weird thing is that I can export to Excel immediately.

If you can save the Excel file to text then this would give you an indication of whether the problem is with the size of the text file, or something to do with MS Access.
 

LouisPrefersExcel

New member
Local time
Today, 12:33
Joined
Jun 29, 2021
Messages
6
If you can save the Excel file to text then this would give you an indication of whether the problem is with the size of the text file, or something to do with MS Access.
Hey,

Thanks for answering, the size of the Excel file is 9kb so I believe it's something to do with Access.
 

LouisPrefersExcel

New member
Local time
Today, 12:33
Joined
Jun 29, 2021
Messages
6
Hi. Welcome to AWF!

Have you tried linking to the tables in the external database and executing a query export locally?
Hey,

Thanks for answering. I haven't tried this since there are actually 8 external databases (currently separated for use by different parts of the business, this was setup before I joined the company). All of the databases give a similar output so I was going to mimic my query/macro once setup across each of the databases.

I may still give this a go though and see if it works, cheers.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:33
Joined
Sep 21, 2011
Messages
14,038
So why does it need to be text, and not just an Excel file?
 

LouisPrefersExcel

New member
Local time
Today, 12:33
Joined
Jun 29, 2021
Messages
6
So why does it need to be text, and not just an Excel file?
I don't think it has to be text, that's just what I tried first and was curious as to why that didn't work at all but Excel was perfectly fine. Just in case I encounter the problem again but do need it to be text.

Access is all very new to me so still getting to grips with it all.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:33
Joined
Jul 9, 2003
Messages
16,244
the size of the Excel file is 9kb so I believe it's something to do with Access

Sorry my question wasn't very clear, what I meant was can you export the Excel contents to a text file? If the export is not successful, then we can assume it's not so much MS Access, but something in the data.
 

LouisPrefersExcel

New member
Local time
Today, 12:33
Joined
Jun 29, 2021
Messages
6
Sorry my question wasn't very clear, what I meant was can you export the Excel contents to a text file? If the export is not successful, then we can assume it's not so much MS Access, but something in the data.
Ah okay, I've just tested quickly with the Excel file and that had 0 problems exporting to txt. It's not a problem, I've decided to link the Excel export created from the external database to my current one and I have an append query to update the tables within my database. This seems to do the trick, whether it is the best solution or not I am not sure.

Thank you.
 

Users who are viewing this thread

Top Bottom