MS Access - Parsed Text File as RecordSource

We have a process (VBA) that reads a text file line by line looking for particular pieces of data that gets inserted directly into an Access table.  The issue I have is we never get to see the data prior to committing it to the db.  FYI - we do all kinds of field mapping and dynamic sql inserts as the data comes from various sources and various layout...

We have a form that has a continuous subform to show the data as it would be imported... Right now (with non-text files) we set the RecordSource with a dynamically created Select statement from a linked excel file.

I'm trying to figure out if there is a way to use the parsed text file data as a recordsource that we can run a sql query against that would allow us to use the same subform as mentioned above.

I'm tying to avoid creating temp tables as I want to minimize Access's bloating thus requiring compact/repair to be run all the time (we import a lot of data so managing the size of the db is important).  I'm wondering if there is a way to send that parsed text data into an variable or some object that we could use as a recordsource...

I'm scratching my head on this.  Any thoughts are appreciated.

A Note - right now we are all Access as a frontend and backend.  In the near future we will move to SQL Server backend...
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
You might also want to consider creating the temp tables in a "temp db".  My article on using temp tables in Access discusses this and provides a function that allows you to build those tables quickly, either in your front end, or in the "temp db".  If you choose to go the "temp db" route, it will automatically link those tables to the FE for you.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
keschusterAuthor Commented:
Very cool fyed...  I'm wondering about SQL Server - as we will be moving to that eventually and I haven't worked much at all with sql server / access combo.

I know temp tables in the sql server world are used all the time.  How would you create, link, and destroy a temp in SQL server from Access....

I kind of think if I can figure that out I might have a solution...
keschusterAuthor Commented:
fyed - I've been playing with your temp table code...  I'm curious - in the article you say the you drop the tables at close...

How do you deal with the bloat in the temp table.  Adding tables, populating, then dropping will still cause the db to bloat won't it?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.