• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

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...
  • 2
1 Solution
Dale FyeCommented:
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.
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?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now