I want to do this the easy way but I don't know how.

October 25, 2011 8:55 AM

How do I efficiently pull this data from an Access 2003 database.

I have a 90mb database in an .MDE format made with Access 2003. It contains hundreds of tables, queries, and forms. I have been tasked with collecting a bunch of information on a specific set of accounts.

There is a main switchboard that will let me search by account number and then take me to another switchboard that will let me view some forms (I think they are forms anyway) specific to that account.

What I've been instructed to do is to view each individual form and then print that to a PDF in a folder for that specific account. I have a list of some 450 accounts on an excel spreadsheet that I need to do this for. I end up with around 30 files per account and this whole project will take me about a month if I have to keep doing this this way.

Since this data is going to a party outside of the company I can't just send them a copy of the full database since it has personal customer information that they aren't authorized to access.

I would like to do one of the two following things:

1. In a perfect world, I would simply delete all of the records except for the ones that are relevant to my 450 accounts.

2. In a slightly less perfect world, I would have a report that would use the account numbers from my excel spreadsheet and then spit out what I need in something readable with one file per account number.

My problem is that I don't have anything but the most basic of knowledge of Access.

I might be able to get a copy of the .MDB version of the database and I might be able to find someone with the database knowledge to actually do this stuff but I need to be able to make a compelling case for it.

My questions are: Are either #1 or #2 possible? Is there some third option I should be considering? How much of an expert do I need to find to be able to do this? How long would it take them to figure it out and make it work?

posted by VTX to computers & internet (6 answers total)

1. So if you delete everything then that's not relevant then you can send the entire database over? That sounds like the way to go if the database structure isn't too complicated.

What I would do is import the list of account ID's, then use a query to delete records with ID's that are NOT in the imported list.

The query would be something like this:

DELETE FROM table1 WHERE recordID NOT IN(SELECT id FROM table2)

Microsoft has a number of articles on deleting records from Access:
http://office.microsoft.com/en-us/access-help/delete-one-or-more-records-from-a-database-HA010077146.aspx?CTT=5&origin=HA010096302

2. Use Visual Basic for Applications to do this. Since it's going to take you 30 days to do this manually, spend a week learning VBA programming and you'll be able to program something to do it automatically. The time trade-off will be worth it and you'll have a useful skill for the future.
posted by yodangson at 9:29 AM on October 25, 2011 [1 favorite]

Completely agreeing with yodangson. Learning to write queries has paid off for (literally in $$) many times over.

Learning to run a little query is not all that difficult even for a person with the basic knowledge you have. Copy the query above, learn how to get to the editor, paste and adjust the names to match your table names. Run the query or export to a file. Done.

And for your sanity, make a back up of the database first and work on the copy if possible. Once you're done, you can delete any duplicate files. In fact, definitely get rid of the redundant files. The last thing you want is for people to be entering data into two different, yet similarly labelled databases.

Access can be a bit unforgiving about getting rid of records and providing all too easy ways to do it. And once they are gone, that is pretty much it.
posted by lampshade at 10:24 AM on October 25, 2011

You can't write or edit VB code in an .mde file, can you?

I don't think you need to delete any records, but I do think it is always a good idea to work on a copy if you are going to make changes and you did not design the original. You will need to know how to write queries and how the tables are designed. Hopefully they will all hold account numbers and then have different information in the rest of the columns. To get info from two or more tables, you have relate them by account number.

What I would do:
1. Import the list of account numbers from Excel into Access. From the Tables section, right click and select Import and find your Excel file (close the file before you try to import it). Go through the steps in the wizard. What you want to end up with is a table that is just one column that has all 450 accounts numbers in it. If there is anything else in the spreadsheet that you need and can't get in the database (like a customer name or something), then include those columns as well. Name this table "Sample List" or something meaningful.

2. Figure out what fields are displayed in the form (easier in the .mdb than the .mde since you can see the design view) and what tables hold those fields.

3. Write a new query or queries (in design view since you are a beginner) that includes Sample List and any other tables you need. Relate them by account number.* Since you said each of the accounts has about 30 forms, you may be able to combine some of them into one query, but you will probably need a couple different queries.

4. Each of these queries will be pulling all 450 accounts and whatever fields you have requested. So, you should be able to just export them to Excel and have the data in an easier to mess with format for whoever you would be sending the pdf files to.

*Maybe not all of your tables have account number in them. You will have to figure out what relationship they have to the rest of the data then. An example is if you have one customer with multiple account numbers. You may have a table that only includes Customer and other info that you need but no acct number, let's call that Table 2. In that case, you take Sample List, relate Account number to Account number in another table (Table 3) that has both Acct number and Customer in it. Then you relate Customer Table 3 to Customer in Table 2. Now you can pull things from Table 2 that match your account numbers in Sample List.
posted by soelo at 11:03 AM on October 25, 2011

You might find Altova's Mapforce application interesting. It lets you connect to one or more data sources - such as your Access database. It can them provide output in various forms: a CSV file, a spreadsheet or a table of entries in another database for example. You can connect source and destination attributes and choose any logical transforms that you would like to do. As with query writing, Mapforce proficiency takes a while- but the payoff is big if you need to do this sort of thing often, fast, without much in the way of scripting and from a diverse set of sources. The product is quite expensive but you can get a 30 day evaluation to give you an idea of whether it would be right for you.
posted by rongorongo at 11:15 AM on October 25, 2011

You can't write or edit VB code in an .mde file, can you?

Yep, I can import the tables and queries into a new database but, since I don't really know what I'm doing and I didn't build the thing, I don't really know what is in those tables and I can't see where the forms are getting their data from either.

So if you delete everything then that's not relevant then you can send the entire database over?

Yes, if I could delete all of the irrelevant (read: personal customer information that we can't give this outside party access to) data, I could send that copy of the database instead with only the information for the 450 accounts that we can let them see.

That sounds like the way to go if the database structure isn't too complicated.

Assuming that I could get my hands on a copy of the database in an .MDB format, the other issue is that there are over 400 different tables and I have no idea how all of them are put together but I doubt I'll be able to get the a copy of the original .MDB in any case.

The most likely scenario is that I'll have to dump this off on whoever designed/is responsible for the database.

If I had the prerequisite knowledge and proper access, how long would it take me to do this through either approach?
posted by VTX at 12:14 PM on October 25, 2011

If you had the knowledge and access, this might take at most a half day to write the code. The speed of a conversion of a query to 30 page reports to PDFs will depend on your computer's processing capabilities.

Whatever you do, don't try to perform this over a wireless connection or VPN. :/
posted by desjardins at 3:00 PM on October 25, 2011