Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2009
    Posts
    21

    Unanswered: Basic Script Writing

    Recently the company I work for made the move to Microsoft Access from a DOS based version of Paradox -- and I've now inherited the task of converting the legacy code into Microsoft Access Queries and Macros.

    Right now I've gotten most of the work done... the system I'm building is supposed to update mailing lists and customer records -- for example we'll have a central database of records and as we build up clients in different regions we'll sync the regions to the central database. If the customer dosen't exist, we'll copy them in, and if they already are on file, we'll update the information if there's changes.

    Right now I've been using queries and SQL as I don't know VBA, but despite my efforts, the product I've made never produces tables with the final result as the original.

    I've attached the scripts as .txt files and while I know PAL is long gone, it's fairly simple to get the concepts. Any assistance is greatly appreciated.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    macros are fine if you don't want or don't need to dip you toes into a programming environment. However, I'd argue, that sooner or later any serious app is going to need to develop code.

    Access is a sort of OOP environemnt, you put code behind specific events in forms or reports. Plain vanilla Access can probably do about 70..80% of the work for you, the more mundane stuff, the sorting and grouping and so on. its the fianl tricky 30..5% that you often have to dip into Access, unless you are using a server backend, where probably you have to do say 30..70% of the work yourself

    However I have no wish or interest to look at PAL, I have no intention of understanding how it works (unless theres a customer paying for my time that is) so Im not going to look at your attachement).

    there is always one thing that disturbs me when an old system is being killed off of the prerssure always seems to be I must make the new system look identical to the old one.. if you want identical then keep the old system.

    you could achieve what you want to do by judicious use of queries. import the new data into say temp tables or link the new data in its update db
    join the master customer table witht he satelllite customer table
    depending on how you structure the join you can then identify
    customers in the master db that don't exist in the satelllite db
    customers in the master db that also exist in the satelllite db
    custmers that exist in the satelllite db that dont' exist in the master db.

    using those queries as a feedstock you can then take appropriate action and process the data as required.
    the concern I have is how do you know which is the correct data. not knowing you app it sounds like you are asserting that the satellite db is the "design" authority.. ie data in there supercedes the data int he master "central" db. if so you run a two stage process
    you update those records that exist in the central db, then add new customers
    all of this can be accomplished by an overnight process kicked off say by using windows scheduler

    however it sounds to me as if your system shoudl really be a centralised server based db and get away from these problematic imports and exports
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2009
    Posts
    21
    Thanks for the information. My predecessor actually also worked on developing a PHP backend/web-interface for the database system and while I was able to make great headway with modernizing the code (it was from three years ago -- untouched and unused) my boss switched gears to Access because he wanted a simple front end as generally him and the other staff are the primary users of the system.

    I probably could set up the PHP backend without issue and create a few forms in access to link to the server, however right now what I' m getting from the code errors is that something is wrong with our duplicate validation. Currently when updating records between the central & satellite, the database will check the timestamp of when the record was updated so if the satellite happens to be older, we won't change anything, and if it's newer, the database would update.

    The main reason I'm asking about VBA now is because the old system used scripts which although not complex, appear to be more precise than basic SQL.

    Do you know of any good references or tutorials or sample code sources so I can get an idea of the different options?

    Thanks,

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Use access on top of sql server if your boss is adamant about making his own front end. If he's not talking about his own front end, than you can write whatever "simple" form he wants to see in the language of your choosing so it's a non-issue anyway.

    The wise money here says you will be migrating AGAIN when/if Access's very limited scope becomes insufficient for current business needs. Leapfrog that whole step and go straight to a platform designed for multiple simultaneous business users.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    jut to clarify Teddy's comment
    the problme isn't so much with Access.. it can support thousands of concurrent userrs, the problem lies with the back end storage mechanism, the default storage mechanism in Access is JET which runs out of steam anywhere between 15 and 50 users depending on who designed the the Access application. you sill have similar problems if ou try to get the same number fo concurrent users in VB, VB.NET, C, or whatever.

    so if you want to use Access as your fornt end thats fine, but don't be tempted to use JET use a server backend
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2009
    Posts
    21
    Okay, thank you both for the assistance. I guess I'll just migrate over to SQL, and set up the simple front-end for the time being since the company is currently three employees.

    I'm just glad that we gave up on Paradox for Windows (our first attempt at modernizing the systems a few weeks ago, although I heard development on Paradox stopped two years ago) -- my boss wanted to stick with Paradox over Access because he believed it would be easier to migrate, however after hours of working with the system and trying to find decent support, we finally switched to Access.

  7. #7
    Join Date
    Jan 2010
    Posts
    4
    If you have not resolved your issue or received the support you are looking for, I am an expert level developer of Paradox and ObjectPal. I will be happy to assist you. EMail me directly (john.chambers121 at gmail) with contact info and I will provide the assistance you require. Thank you. John

  8. #8
    Join Date
    Sep 2006
    Posts
    265
    With only three employees Access is fine although version of Microsoft Servers OS come with a five user licence for SQL server.

    You can write your Macros and then convert then to VB to give you a leg up so to speak.

    Another alternative is php is a Terminal Server or Sharepoint (MOSS) although with the small number of users it may not be appropriate due to the cost. You have one central database that everyone uses.

    There is a lot great facilities in Access I would rather use Access' forms to a web based form but that is only my opinion.

    Simon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •