I create MS Access databases to convert .csv files to fixed-position text files. I give the databases names such as ‘123456’. The files I import would be called something like ‘123456_Current’. Now here’s the thing: Scattered around my database libraries are databases called ‘123456_Current’ (with different numbers, obviously). I did not create them. I open a database, import a .csv file, and export it. I don’t create a new database.
Why does MS Access create these new databases? Why does it not create one for every file I import, instead of only occasionally?
are you sure that those other files really are databases? Check their file type. I have been in your situation and those other files were actually shortcuts to things like “recently opened files”.
Do you ever leave Access running after you’re done with an import / convert job? Does your PC ever crash?
This is speculation … I’d expect Access to create a temp database for that workflow and then delete it when you’re done & close out Access properly. But they might be left behind if Access o the PC crashes instead.
It might be interesting to look at the dates of these unexplained leftovers.
They are .mdb files, and when I open them they look like the ones I intentionally create.
I leave Access running as long as I’m working. My PC hasn’t crashed in quite some time. I can’t remember the last time, or if this particular machine ever has. Sometimes when working from home RDC will stop responding or lose the connection. There’s no telling if the loss of the RDC connection has anything to do with the extra databases.
But let me get this straight: I open the 123456 database and import 123456_Current.csv. Something happens (or Access just decides on its own) and a new database is created called 123456_Current.mdb. Have I got that right? Why would it create a new database with that name, but no imported file in it?
With this last explanation I’m wondering if sometimes you don’t inadvertently hit [save] or [save as] and thereby create a copy unwittingly. A side effect of fast computers & small datasets is even seemingly large operations can happen behind the scenes in a twinkle.
I never use the Save button. (Save As required pulling down the File menu.) And now that I look, it’s greyed out. Also, why would it never save 123456_Previous?
My experience is that if I open an Access database and immediately close it without making changes, the timestamp is updated anyway. (That doesn’t happen with Word and Excel.) I wonder if the OP’s problem might be related.
How exactly are you expecting MS Access to import without having an Access database? From my experience with it, it’s a desktop database program with import/export capabilities. It’s not a standalone import/export library, and unlike Word or Excel, it’s not designed to work with an ‘unsaved document.’
My guess is that Access is just automatically backing up your database to avoid losing data. Whether it makes a backup or not depends on how long you keep the database open. All Office programs make backups like this, to avoid losing data. It seems odd they wouldn’t be deleted after you close Access, though.
On an unrelated note, may I ask why Access is part of your workflow? Are you actually messing with the database before converting it to a text
Report? Because just changing a CSV to tabbed columns should be right up Excel’s alley. Open document, select all, copy and paste into a text editor.
I use Easytrieve, which requires fixed-position text files. Tab-delimited won’t work because the columns don’t always line up. (I tried it again yesterday.) Saving an Excel file as text doesn’t work because after a certain field position, the end of each record is lopped off and appended to the bottom of the file. There was a different way of saving the file as text, but it also had a problem. So the only way I can get a fixed-position text file is to save it as a .csv file and import it into Access, and then export as fixed-position text, defining the position and length of each field.