Subject line says it all, or most of it. I’m interested in learning to use Access, and there’s nothing like doin’ if you’re up for some fast larnin’.
What I’d like to do is build a form that automates a rather tedious task that nobody seems to want to do. Specifically, the Access database should do the following:
Allows the user to click a button to browse for and import an Excel spreadsheet to an Access table.
At the click of another button, a series of SQL queries are executed to make the table usable.
The desired, much smaller table is spat out (as a matter of fact, it’s two tables, but I don’t anticipate any trouble in this area).
I know (almost) exactly how to do steps 2 and 3, and the SQL queries are all ready to go and working perfectly. I’m hitting a snag on step 1, though, and am having trouble finding a sample of VBA code that would do step 1. Is it even possible? Has anyone done this before, or know where I can find info on this?
Once this is done, a task that used to take the better part of a day for most people would be completed in roughly 30 seconds. Like I said, I’m very new at this, and I’m not doing this because I’m considering a career as a developer down the road. But I would like to be able to leave something useful behind after my consultant position ends.
In addition to the DoCmd transfer, you should probably take a look at this; it does the API window to allow your user to select the file. (Otherwise you have to somehow ensure that your user correctly names the file to an exact filename each time.)
There’s an example at the top to show you how to call it. Set a variable to call the API, so that the variable contains the file name & path. Then you can use that variable in your DoCmd line.
I’ve just done several of these (although using delimited files rather than Excel), so I’m game for questions if you have specifics.
Don’t forget to include cleanup routines - are you appending data, or is it a separate set of data for each run? If it’s a separate set, you’ll need to clean out old data. If you’re cleaning out old data, do you want to archive it or just dump it?
And error-checking. Don’t forget the error-checks, or some user will end up off in the wild blue yonder without a boat or a paddle.
Thanks, guys, I managed to track down an import sub from what you’ve said, and it seems to be working. I haven’t tried getting it to open the browse window yet… that code seems deeper, and I’m still trying to wrap my head around it (I don’t generally try to use code in any language until I understand what it’s doing). But I will figure it out… and perhaps I’ll be back with another problem I can’t solve.
I’ve also got some formulas to put tags on each row. I really wish I didn’t have to do this in Excel, but I don’t see a simple way to do this step in Access.
First formula is for a field called Order. As it sounds, it increments by 1 for each row:
=a2+1
Second formula is for sessid. It looks for a zero in another field which indicates the end of a session, and assigns an id for each session:
=if(h2=0, b2+1, b2)
Third formula decides whether the session’s a keeper. It looks for a magic number and makes a note if we’ve got a winner:
=if(m2=34567812, “keep”, “”)
All pretty simple, but I guess Access requires a non-simple approach. I can’t really imagine a way to do this in SQL, and again, I don’t really have quite the vocabulary needed to get what I need out of Google to do this in VBA.
Is this do-able (even if I’m admittedly using the wrong ap), or am I going to have to open that 20meg Excel file at some point after all?
Is the Order already set, or are you trying to set it? Access tables have a datatype “autonumber” which will assign consecutive numbers to your records. (You’d just create the column “Order” and set the type to “autonumber”.) Is that what you’re trying to do?
This sounds like your db structure is off; probably the session info should be split into a separate table. (I’m assuming there’s multiple sessions per one something in your data.)
However, given that you’ve got a table with column Session (B) and column EndOfSession (H), you can do the same thing in Access.
UPDATE YourTable SET YourTable.Session = IIf(YourTable.EndOfSession=0,YourTable.Session+1, YourTable.Session);
Same thing.
UPDATE YourTable SET YourTable.Keep=IIf(YourTable.KeeperNote=34567812,"keep","");
I wouldn’t go so far as to call it simple, but it’s not that hard to automate Excel from within Access VBA. There’s a nice tutorial explaining how to do that here:
That board is a more suitable place for getting help on your project in general.
Thanks for the tips… I’ve checked in with vbforums and given them a more detailed description of my problem. Got a feeling I’ve got a trip to Barnes and Noble in the near future, though.
