Importing external data, then parsing it!

[Torchwood]

Following on from my previous Newbie question, I'm slowly starting to find out about different aspects of Access, and considering the sorts of problems I'm going to have to solve to get my planned Database up and running.



I have 2 more Newbie questions for you (and I'm being too lazy to split them into separate forum threads, sorry):

Q2 - User friendly front end. Can anyone recommend any good articles on the web on how to go about designing/implementing a user front end that does the job of hiding all the guts of Access from the normal user? I'm sure I've seen other databases where, on opening, the user is only presented with a nice series of forms - they cannot see any of the normal Access menus, tables, etc. Roughly how is this done? Is it an inbuilt function of Access, or is it all done with VBA?

Q3 - As per the thread title ... one job I know I will have to do is to import data from an external source. I'm getting familiar with the idea of SQL queries that can return or process lots of rows in one go, but I'm not sure how to look at individual rows one at a time. My previous experience has all been in VB and C, and I imagine if I were using those languages I'd end up with code something like:

Given array T1() containing imported, unprocessed, data and array T2() containing final processed data ...

+ After an import:
+ Loop through each entry in T1
+ Try to find the appropriate 'matching' entry in T2
+ If found, update the T2 entry using data from the T1 entry
+ If not found, create a new T2 entry, using data from the T1 entry
+ Repeat for next T1 entry until at end of T1
+ Delete all data from T1 in preparation for next import

Can someone give me a clue as to how this might look in Access. Obvioulsy, I assume VBA will be involved, but I'm not sure how the basic structure of the code might look - if you can suggest key VBA functions, then I can Google them to see how they work in more detail.

Thanks!

[June7]

Q2 - for start, review
http://www.addintools.com/documents/...s-startup.html
http://forums.aspfree.com/microsoft-...at-303789.html
Be aware that holding shift while opening database will override most of the start up options

http://forums.aspfree.com/microsoft-...me-206240.html

Q3 - not enough info to advise, what do you mean by 'parsing'? sql actions (UPDATE, INSERT SELECT) might accomplish this (without VBA), otherwise VBA would involve opening recordsets and looping through recordset, gets complicated.

[Torchwood]

Q2 - Thanks! Just what I needed.

Q3 - OK. I guess I might as well describe my planned database in more detail, for this and future questions!

My company uses an old mainframe system for controlling our project funding, and another one for controlling purchase orders. Both function adequately well together, and it is possible to get all sorts of reports out of them for our management to review latest status on a project. However, the one thing they don't do at all well is to help us plan for the future. By this I mean the systems are very good at telling me how much money I've spent and how much I've got left - but there is no facility for planning what expenditures I might want to raise in the future and therefore analysing whether I've got enough money for all those planned future expenditures.

In the past we tried using an Excel spreadsheet for this planning, and this worked to a limited extent. But we have a significant problem with maintaining the spreadsheet; a user can enter a planned future expenditures into the spreadsheet, but when they actually get around to spending the money we have to rely on the user updating the spreadsheet. If they don't mark the 'planned future' expenditure as an 'actual past' expenditure, it effectively gets counted twice.

I know I can get exports from our mainframes to tell me in detail what money has actually been spent, so in theory I should be able to add some VBA scripts into my spreadsheet to compare the mainframe exports with the user-entered data and automatically change status to 'actual past' where appropriate. However, for this sort of script to work the user-entered data needs to be fairly strictly controlled (correct data in correct cells). I've a fair amount of experience with Excel, and I'm not convinced Excel is the correct tool for this job ... so time for me to investigate Access !!

So, moving on to Q2 in more detail; I'm assuming I'll have 2 tables (well, it'll be more than that because 'expenditures' will be made up of purchase orders, and each order may have revisions, and each revision will have individual purchased items, etc., but for now in my experimental phase I'm going to ignore that).

T1 will contain my 'imported' data containing details like: purchase order number, date and monetary value
T2 will contain the user entered data containing: purchase order number (if known), estimated monetary value, description, status (i.e. planned-future, actual-past), etc.

At this point, I haven't got around to considering all the different possible use-case scenarios (e.g. user has not filled in a correct order number, etc.), so for now, lets just assume the user-entered order number is correct. I guess I can also assume my data entry forms are designed such that the user cannot enter duplicates.

On import, I want to scan T1 for every purchase order and update all the equivalent entries in T2 - update the estimated monetary value with the actual monetary value, change status, add in new records to T2 in case the user has completely forgotten to enter the purchase order.

How would I do this with a query? And (as I'm still experimenting and learning), how would I do this using in VBA using recordsets?

[June7]

Answering Q3 would involve writing a book here, which I am not willing to do. There are plenty of references for learning query basics and programming with recordsets - Access Help, web, books. Here is one article on using recordset http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Here is a book you might find helpful: Access™ 2007 Programming by Example with VBA, XML, and ASP by Julitta Korol, Wordware Publishing, Inc.

What is the database program on the mainframe? Access can set links to some databases.

[Torchwood]

OK, no problem - thanks! I've been scanning Google for SQL related stuff and am coming up with lots of interesting things, but sometimes you need the correct keyword! Now I know I need to look into 'recordsets' I've got somewhere to start.

I'll do more research and perhaps come back to this thread when I have a more specific question.

[June7]

Here is a simpler example of manipulating recordset http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[Torchwood]

Thanks! Will be useful ...