Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to read SQL in Access97? (97SR2)

    I have been sent a PKZip file containing 148 files with the following extents: DAT, DB, DOC, FMT, MDB, ZIP.

    I am to demonstrate that I can make changes to the database data, and thereby establish enough confidence in the prospect that they will become a client and send me money. I need the money, so I need the prospect to become a client, so I need to know how to hook up to an SQL database.

    I am ignorant, honest.


    1) I still think in terms of an "Access database", but maybe I should start thinking of a "database" with "SQL" or "DAO" or "ADO" or whatever engines? yes??


    2) I have been writing Word97/VBA using DAO to access Access97 databases and perform truly useful work on them. I am familiar with opening an Access97 database in Word97/VBA and creating a workspace and obtaining a record set using a SELECT statement.

    3) I'd appreciate very much the bare minimum of VBA code to do the same thing in SQL.

    Yes, my brain is here, but I've not been exposed to much of the concepts of MSOffice databases.



    I tried to open the MDB files in both Access97 and Office XP with no apparent success.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to read SQL in Access97? (97SR2)

    DAO and ADO are object models. They're what you use when you declare and manipulate a recordset. SQL is a query language, and that isn't what you need.

    You should, in fact, be able to handle most of those files using ADO but you need to know which provider they belong to. You won't be able to handle zip files, you'll have to unzip them first. I have no idea what an FMT file is, so you're on your own with that. If it is, by chance, a FileMaker file, you're on your own anyhow because the format is awful and not IMHO any kind of database.

    DAO is Jet-specific, so the MDB is really the only one that will work properly with unless you can create a link to a table/document using ODBC. If you can't open the MDB directly, there must be a reason, and that suggests you won't be able to open it in code either.

    As for the code request, you aren't asking for *anything* simple, Chris. Try XP's on-line help for samples of using ADO to open a recordset. They'll mostly use either SQL Server or Access in the examples but you can extrapolate from there for the other providers. Each OLEDB provider will have a different set of methods and properties to work with and the syntax has to be right or you'll get a (usually) silent error, so your code will be different for each provider. Then go out and get yourself a good (it'll also be expensive) book on ADO (my favorite is Mike Gunderloy's "The Visual Basic Developer's Guide to ADO") and study hard.

    This is not something you can tap dance over. Sorry.
    Charlotte

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: How to read SQL in Access97? (97SR2)

    FMT files can be dBase IV, FoxPro (I think), Lotus 123, SQL Server format files or even Schedule+ related, among others. Which should make connecting to them interesting! I'd agree with you about FileMaker too (I've only used it twice but that was enough for me.)
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to read SQL in Access97? (97SR2)

    You're right. I had forgotten about the FMT files that go along with the user interface in several of the apps you listed.
    Charlotte

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to read SQL in Access97? (97SR2)

    > This is not something you can tap dance over. Sorry.

    Charlotte, thanks for the response. You need NEVER apologise to me about telling me "it's a long road". I'd much rather know there's a long time ahead, or that a task is truly insoluble, than spend hours trying to find a solution that doesn't exist.

    I am going to go back and search posts in this Forum regarding the points you have raised. If i get around to creating a "paper of definitions", I'll post it here for peer review.


    The files have arrived from an accounting package. The file "Glcoat.dat" is a General Ledger Chart Of Accounts T(?)able. I can see the account names and numeric codes.

    The prospect has asked me to write a package that makes subtle changes. I have a feeling that there is a "better" way to do that, rather than a Word/VBA GUI interface. I will continue to explore the file types, if only to see if I can crack them.

Posting Permissions

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