By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 3,094 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Orientation to more complex Access usage

P: 2
I should be most grateful for a little advice.

I have used Access 2000 & latterly 2002. Am about to upgrade since it is evident that documentation, tutorials etc are more readily available in later versions.

I work on this on my lonesome & do not have access to a mentor or tuition. This is an intermittent activity and quite adjunct to my "normal" investment activities.

I have found that most books & documentation are either "relatively" basic (what is a table, Query etc - How to create or vary them etc.....) or else in one leap, without transition, operate, biblically or encyclopaedically, at the VBA SQL level in a "begged question" world of syntax & grammar, which I am completely bewildered by. If I need to put myself through this particular form of purgatory (since after all its 30 years since I programmed & indeed taught programming in procedural languages!!) then I will. I was rather hoping to learn as I go.

To date my approach has been OK. I have used Access largely via wizards for input & reporting purposes largely for tax & cashbook purposes and not having to use it beyond this has not plunged me into VBA or SQL, since Wizards et al plus a little redesign here & there have done the job! Thus I haven't needed the bibles.

I am now doing things that are somewhat more complex, (portfolio management software using records ex Excel which are provided by an external invesment manager) and evidently I will now need to probe deeper. But where???

I do not seek advice as to technicalities rather would I be grateful for a little direction finding advice.

I believe that a little intellectual charity regarding the following question would set me on my way. I should be most grateful for this.

The Excel data that I receive is somewhat dirty. I have no choice in this. I have to clean it up, record by record and convert certain codes (Security Codes) (or create additional codes) by "look-up" ex tables that I have created for the purpose, into the more useful coding system that I have conceived for my “fully” normalised database. A variety of other processing things need to be done to create the "clean" records that, in association with the other tables of the system, I will then utilise for the various reporting that I have in mind.

It seems to me that to do this I need to analyse and enhance the Excel data record by record once it has been imported into my database and an Access table has been made of it .

I wish to do this automatically rather than "manually". I have established the processing "algorithms" that I need to deploy.

My notion is that one or another type of query is designed to do this. I am conversant with the various types of query that there are, though I use them more or less n their standard form (ie I have not yet written done any programming in association with them).

If this is correct, how do I, say, look up the (Security) codes that I wish to replace. How do I do certain conditional processing on these records & how do I create new fields within any “output” records.

Is this where VBA & SQL come in? ( & by the way what’s the difference between these two??) Also if in the processing of these records I need to use VBA or SQL, how do I associate any code I might come to write (once I’ve learned how to write it) with the input records as they come to be processed & updated.

I am sure that my infinite ignorance has been exposed by the above but some forebearance on this learned forum’s part would be most appreciated.

I have many, many questions but I do believe that answers in respect of the above, which are but an abstract, will put me on my self-learning way again.

In anticipation - Most grateful.

Pippapippa
Jan 7 '07 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,616
I should be most grateful for a little advice.

I have used Access 2000 & latterly 2002. Am about to upgrade since it is evident that documentation, tutorials etc are more readily available in later versions.

I work on this on my lonesome & do not have access to a mentor or tuition. This is an intermittent activity and quite adjunct to my "normal" investment activities.

I have found that most books & documentation are either "relatively" basic (what is a table, Query etc - How to create or vary them etc.....) or else in one leap, without transition, operate, biblically or encyclopaedically, at the VBA SQL level in a "begged question" world of syntax & grammar, which I am completely bewildered by. If I need to put myself through this particular form of purgatory (since after all its 30 years since I programmed & indeed taught programming in procedural languages!!) then I will. I was rather hoping to learn as I go.

To date my approach has been OK. I have used Access largely via wizards for input & reporting purposes largely for tax & cashbook purposes and not having to use it beyond this has not plunged me into VBA or SQL, since Wizards et al plus a little redesign here & there have done the job! Thus I haven't needed the bibles.

I am now doing things that are somewhat more complex, (portfolio management software using records ex Excel which are provided by an external invesment manager) and evidently I will now need to probe deeper. But where???

I do not seek advice as to technicalities rather would I be grateful for a little direction finding advice.

I believe that a little intellectual charity regarding the following question would set me on my way. I should be most grateful for this.

The Excel data that I receive is somewhat dirty. I have no choice in this. I have to clean it up, record by record and convert certain codes (Security Codes) (or create additional codes) by "look-up" ex tables that I have created for the purpose, into the more useful coding system that I have conceived for my “fully” normalised database. A variety of other processing things need to be done to create the "clean" records that, in association with the other tables of the system, I will then utilise for the various reporting that I have in mind.

It seems to me that to do this I need to analyse and enhance the Excel data record by record once it has been imported into my database and an Access table has been made of it .

I wish to do this automatically rather than "manually". I have established the processing "algorithms" that I need to deploy.

My notion is that one or another type of query is designed to do this. I am conversant with the various types of query that there are, though I use them more or less n their standard form (ie I have not yet written done any programming in association with them).

If this is correct, how do I, say, look up the (Security) codes that I wish to replace. How do I do certain conditional processing on these records & how do I create new fields within any “output” records.

Is this where VBA & SQL come in? ( & by the way what’s the difference between these two??) Also if in the processing of these records I need to use VBA or SQL, how do I associate any code I might come to write (once I’ve learned how to write it) with the input records as they come to be processed & updated.

I am sure that my infinite ignorance has been exposed by the above but some forebearance on this learned forum’s part would be most appreciated.

I have many, many questions but I do believe that answers in respect of the above, which are but an abstract, will put me on my self-learning way again.

In anticipation - Most grateful.

Pippapippa
From what you have stated, you definately have stepped beyond basic Access functionality and will have to address more complex issues. Since your processing algorithms are already defined, and you wish to automate the enhancement and analyzing of the imported Excel data, I would suggest listing specific details relating to this overall process, e.g. what is the Field Name and Data Type of the Field storing the Security Codes, which Codes need to be converted, what should they be converted to and in what format, what is the name of the Lookup Table and what are its constituent parts, how does it relate to the new Coding System. etc. Provide specifics, and I'm sure you'll get plenty of qualified responses.
Jan 7 '07 #2

NeoPa
Expert Mod 15k+
P: 31,307
SQL (Structured Query Language) is a standard, text based, query language for RDBMSs (Relational DataBase Management Systems).
When you design a query in Access you are, essentially, using an Access wizard to create a QueryDef object, the most important part of which is its embedded SQL. If you look at the View Menu in Design mode you will see an option to view the SQL.
VBA (Visual Basic for Applications) is M$'s programming language for its Office products. It is principally OO (Object oriented) rather than Procedural although it still uses procedures very heavily. Invoking the procedures is done automatically though, and triggered by various events rather than where in the code they appear.
I'm afraid that I can't point you to much more than (Links to useful sites) for tutorial based help, but I can say that the help system, and seeing what's created by the various wizards, can get someone a long way. Especially if, like yourself, they have some experience of similar matters.
Jan 8 '07 #3

100+
P: 1,646
Hi. I recommend stepping away from Access and into vb6. There are enough tutorials for vb6 to make you blind :)

By avoiding wizards and bound controls you will see the bigger picture much more clearly and you will be in control.
Jan 8 '07 #4

P: 2
WillaKawill, NeoPa, ADezii

Thank you for your comments & suggestions to date.

I shall now look at examples of code generated by Access for simple queries that I formulate. That should be a beginning insight into things.

I shall also follow up the tutorial links.

I am not so sure about plunging directly into VB6 since that would seem to detach me altogether from Access which at the moment I see as my lifeline & buoyancy!!

I'm still unclear though how code (VB or SQL) when it gets written within a Query gets to be pointed at rows of a table one by one & then exposes them for processing. In an earlier life with COBOL & Assembler languages records (& their fields) got "Read" and then you knew that you had them for update purposes and ou did your update by "Write" and then you knew you'd done the update!!

How does the VBA or SQL code get pointed at the record??

Evidently I still live in the past but I WILL make the mind shift in time.

I get 2002 tomorrow & 2007 in a couple of weeks so I'll hibernate a bit whilst distilling all this. For feedback, I'll let you know what the "Eureka" insight actually is when I experience it.

Thanx again
Jan 8 '07 #5

100+
P: 1,646
SQL is 'embedded' within the vb code and sent to the database. The database then returns the appropriate rows and columns in an array format that you have assigned to an object called (strangely enough) a recordset.

Expand|Select|Wrap|Line Numbers
  1. MyRecordset.Open "SELECT * FROM MyTable"
You then access the data via this recordset

Expand|Select|Wrap|Line Numbers
  1. Dim MyIDno As Integer
  2.  
  3. MyIDno = MyRecordset("IDNO")
Jan 8 '07 #6

NeoPa
Expert Mod 15k+
P: 31,307
Will has identified one of the ways that SQL can work in Access.
Another way is that SQL 'code' gets executed by the Access SQL interpreter (engine) when a QueryDef is invoked. This can be done with code, or run by an operator (or even a macro but we won't go there - it is a side-street you'll probably have to back-out from eventually).
When you do process through SQL (Either QueryDef or naked SQL) in your code, the engine is still doing the grunt of the work for you, but you have access to the current record via the RecordSet object created by the 'Open'.
See the new Sticky in the Access forum for a link to some example code of how to process through a RecordSet in VBA.
Another way of executing SQL or a QueryDef from within the code is simply to call the code to be executed :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL {SQLString or variable containing SQLString}
There are alternative ways to do this but this way will work as a basic.
Jan 8 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.