Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2009
    Posts
    21

    Unanswered: Paradox to Access

    Recently I've had to start migrating data over from Paradox 4.0 to Access and while I can move the tables over smoothly I wanted to know if there are any reference guides or tools which would help with the migration from PAL code to Access/SQL.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Unfortunately you are reliant on someone on here having done this with a tool before. I don't know of anyone that has (doesn't mean no one has of course). What have you found on Google so far?

    I think I would echo something Healdem mentioned before - we've both done a bit of legacy conversion work and IMHO a straight "conversion" is a bit pointless. I don't know PAL, however I would not be surprised if it is a procedural language. Although Access uses VBA, another procedural language (it has some OO elements too as I think Healdem mentioned but I wouldn't class it as OO), it works best with Relational Database Management Systems which in turn operate best with sets of data. The language for manipulating sets is SQL.

    What I'm trying to say is, instead of looking to precisely translate a line at a time, I would spend several weeks analysing the code, creating a functional document (i.e. what the code does not how it does it) and rewrite the application to the strengths of the new host tools.

    Does this make sense?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2009
    Posts
    21
    Right now I think I have a partial grasp of what you are trying to say, but since I'm not sure I'll repeat what I'm getting.

    The goal isn't to just copy over the functions of PAL code and replace them with VBA where appropriate, but that we need to look at the overall picture and then put those concepts in plain English and from the plain English build the VBA code on the new foundation therefore leaving the legacy aspects behind.

    From my understanding PAL is procedural, however it is not Object Oriented. It actually isn't supported in Windows -- it runs in DOS only due to memory management (or something similar)-- so when Paradox for Windows came out they created Object PAL.

    Since getting this project I've been examining the code, and I've read the last developers documentation which included plain language explanations for each script.

    If my understanding is correct then, it would be best to start from scratch with my queries rather than trying to mimic the functions of the PAL scripts. The only issue however is that I've been trying to use SQL with the tables (created based on the conceptual explanations provided in the documentation), but I'm not getting the same result set as the original. Because of this discrepency, I'm trying to figure out if I should try to use a different language.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes, that is what I mean. However, you don't really get tools that can do that. They can change one loop to another loop, but can't do something "better" in the new language. It's a bit like translation in human languages - a translator can translate individual words well, but they cannot yet get the grammar right.

    Note that IME of translating procedural data manipulation to set based there are often discrepancies - these are often due to bugs in the source code. Define your goals - do you want to replicate the precise operations of the original (bugs and all) or do you want to solve these problems too?

    Also, it is unlikely the language is at fault. More probable is you have either misunderstood the source code or made an error with the new code. Obviously this is a broad statement - we'd need to see specifics.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2009
    Posts
    21
    Okay, thank you very much for the information. When I first took on this development project, my last predecessor tried renovating the PAL functionality into Access (so he just copied all the steps, even if they were obsolete in Access) but ultimately failed. Then when I came on board, I started from scratch but salvaged some code from the last attempt because during my first research I thought it would work, but now it looks like I'll have to replace it with my own solution.

    The main issue with my predecessors solution was that it was way too complex -- one example I know of is that updating a table for a simple duplicate check required about six or seven queries to be run.

    There is one more question that I have about development though. With Paradox, when checking for duplicates, it was required that our datasets be sorted in a specific order before running the query. Does Microsoft Access have this limitation, or is that outdated since Windows can manage multiple tasks at a time?

    Thanks again.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dont get hung up on the number of queries or other seemingly odd things going one. try to uderstand why its been done. I'd hope any de3veloper would have included comments. take some time to understand the way soemone writes their code. if ther developer has used 7 queries that may be the number he feels he needs. for all I know he may be a wizard at JET SQL and other developers may require more than 7 queries. what matters is is the approach consistent and workable.

    take some time to get a decent Access developer book.

    I'd spend more time on making certain the data model is right before trying to develop code. mebbe part of that is fully documenting what the old system does, and ideally why it does that. you don't know if the Paradox app does things that way beacuse of limitations on the paradox environment or the developer.

    ask yourself why you have duplicates. given the model you have that issue becuase you have 1 centrral db with 3 satellite db's which can create new data.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by parajourn View Post
    There is one more question that I have about development though. With Paradox, when checking for duplicates, it was required that our datasets be sorted in a specific order before running the query. Does Microsoft Access have this limitation, or is that outdated since Windows can manage multiple tasks at a time?
    The answer can never be absolute without looking at the code, but the very likely answer is that since Paradox is procedural, the easiest way to handle dupes is by ordering the dataset. This should not be required with SQL - this is because of the nature of sets (nothing to do with windows). Procedural code deals with one row at a time. SQL deals with sets (i.e. groups of rows). The idea is that if you need to perform an action on N rows you can either perform this on each row, one at a time, or on all like rows at once.

    Note that for relational data there should be little to no requirement for checking for dupes - the principle exception being tables that contain data about people. Relational databases are based on the theory of relations - a major part of the definition of a relation is that all rows are unique. This should be defined by Primary Key and Unique constraints i.e. declaratively.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump View Post
    ....Note that for relational data there should be little to no requirement for checking for dupes - the principle exception being tables that contain data about people. Relational databases are based on the theory of relations - a major part of the definition of a relation is that all rows are unique. This should be defined by Primary Key and Unique constraints i.e. declaratively.
    the issue here I suspect is how the central db handles the data created in the satellite db's and reconciles the same, say customer, created in more than one satellite db, or when the central db retransmits the consolidated data to the satellites

    yes a normalised db should reduce the need for duplicate checking, but the original design is prone to creating duplicates. granted it could be handled by a manual procedure
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2009
    Posts
    21
    Hey guys,

    Thank you both very much for your time and assistance. Most likely after the holiday I'll sort through the documentation of both the Paradox and Access developer again, but I'll check it from the new perspective I have. I'm going to try to get the basic foundation built up today, but I'll leave the synchronization until after holiday.

    My guess is that taking a break from this project would help very much. I'll post an update as soon as I have more news.

    Enjoy your holidays!

  10. #10
    Join Date
    Aug 2009
    Posts
    21
    Hey Guys,

    After some time of sorting through the Microsoft MSDN VBA references, I finally was able to put together a module to use in Access which allows for the database to sync the newest data between two duplicates and then remove the older record. The only problem however is that I'm getting errors about not having enough arguments when calling the sub function.

    To be precise, I'm actually getting an error when my parent function has no arguments in the ()'s and then I get the same error when I call the module which also has empty ()'s

    Do you know how I should handle this? I've tried declaring the functions as void so I wouldn't have to return anything, however that did not work and I'm now not sure what to pass.

    I've attached the code sniplit if it helps.

    Thanks again
    Attached Files Attached Files

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please post the code you are using to call the function(s) and what the error message says.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Aug 2009
    Posts
    21
    Sorry about the missing code, it's listed below:

    Function DupDelete(test As String)
    test = DeleteDuplicateRecords(strTableName)
    End Function

    When running it, I get an error saying the function has the wrong number of arguments. The error number is 2439

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol - ok, we'll try something else.

    What if you run this:
    Code:
    Call DeleteDuplicateRecords("Enter the name of one of your tables here")
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    word of caution
    looks like you are usign global variable to store things like strTablename.. don't do it, it may be OK in this instance, but its bad practice, and it makes detecting errors difficult and it increaes the chance of errors
    Function DupDelete(test As String)
    test = DeleteDuplicateRecords(strTableName) //is this line correct?
    End Function

    should it be
    DupDelete = DeleteDuplicateRecords(test)
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Aug 2009
    Posts
    21
    Thank you both for the prompt responses. I tried both however it was changing the function that eliminated the errors. Thanks for the reminder on global variables. Right now I've been working on this over the past few months, on and off, and it's taken so many different directions that I'm now trying to make the leap to the finish line.

    I ran the code, however it didn't delete/modify anything, but there were no errors, so I think I just need to adapt the code to the table field names.

    I'll post as soon as I have more details.

Posting Permissions

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