Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    8

    Question Unanswered: Linking to .dbf file

    Hi,

    I am trying to link to a .dbf file (I think its probably a DB 5 file but not sure). THe file is being used by a caller ID program, and so that program has the file open. I am trying to link to the same table using access but it keeps telling me that the file is in use.

    I can link to this file using Access 97, but using access XP, or even access 2000, it doesnt let me.

    Its something to do with record locking I think.

    If you need any more details please let me know.

    I am also trying to link to another database file used by the caller ID software. With this one, it just says that the "External table is not in the expected format". Again, this table will link using access 97.??

    Cheers



    Danny

  2. #2
    Join Date
    Sep 2003
    Posts
    8
    the database is foxpro I think

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    You should be sure which format this file has. It determines your linking method. You may consider to publish your file to let us determine the format.

    I would try to link when you Caller ID program does not work. Once you have the link (and got the structure), you may be allowod to select the data.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Sep 2003
    Posts
    8
    Hi,

    http://www.premierstats.co.uk/dbdownload.asp

    The above link will let you download the files that I am talking about. I Cant upload them to here, as ones too big??

    The problem linking refers to the callers.dbf file

    Thanks

  5. #5
    Join Date
    Sep 2003
    Posts
    8

    dbf cont..

    OK,

    I have mananged to work out how to link to the callers.dbf file, using the latest forpro drivers, and creating an ODBC link. Now, even tho I can open and view the data, the following update query does work

    UPDATE DISTINCTROW [dir check] LEFT JOIN callers ON [dir check].caller = callers.HPHONE SET callers.HPHONE = [dir check].[caller], callers.NAME = [dir check].[short ID]
    WHERE (((callers.NAME) Is Null)) OR (((callers.NAME) Not Like [dir check].[short ID]));


    I get

    Single-row update/delete affected more than one row of a linked table. Unique index contains duplicate values.

    Whats going on here? Could it be with the primary keys set up in the table??

    Danny

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I'll check it this evening. Or maybe somebody else is quicker than me?!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: dbf cont..

    Originally posted by winbourne
    UPDATE DISTINCTROW [dir check] LEFT JOIN callers
    ON [dir check].caller = callers.HPHONE
    SET callers.HPHONE = [dir check].[caller], callers.NAME = [dir check].[short ID]
    WHERE (((callers.NAME) Is Null)) OR (((callers.NAME) Not Like [dir check].[short ID]));
    Hi Danny,

    Great that you followed my suggestion and figured out the db format.

    You error now is that your join results in more than 1 [Dir Check] per Callers for at least one Caller record.

    However, please be aware, that you specified a LEFT OUTER JOIN, but your result will be a INNER JOIN, since you have additional restrictions on your Caller table. Also, your statement
    SET callers.HPHONE = [dir check].[caller]
    hasn't any effect, since you used just this relation as your join criteria.

    What to do? Make a view V on [Dir Check] as
    SELECT Caller, min([Short ID]) As MinID, count(*)
    FROM [Dir Check]
    GROUP BY Caller

    You may use this view to get your multiplicity, too.

    and rewrite your update:
    UPDATE Callers INNER JOIN V
    ON callers.HPHONE = V.caller
    SET callers.NAME = V.MinID
    WHERE callers.NAME Is Null OR callers.NAME Not Like V.MinID;
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  8. #8
    Join Date
    Sep 2003
    Posts
    8
    I am gettings

    "OPeration must be an updateable query"

    Any ideas why that might be happening.

    I created the view V, as a query?

    Danny

  9. #9
    Join Date
    Sep 2003
    Posts
    8

    Question updating external dbf file

    Basically, lets clarify what I need to do here.

    I have the .dbf database file (still not sure what format, as even though, I can link it using the ODBC Fox Pro Drivers, I can only do this when it looks in a free table directory, as apposed to actual visual fox pro database. What other databases have got .dbf extensions</b>. The file in question is available at http://www.premierstats.co.uk/dbdownload.asp. Its callers.dbf. The calls .dbf file lets me connect directly, but the callers.dbf doesn’t. (By that, I can just use link tables, select .dbf files, and it links). With callers.dbf, it says "Unrecognized Database format". Everything used to link with Access 97, but when I upgraded to Office XP, things started to go wrong.

    So, I don’t actually have to link to the file, I just need to update is data every so often. The data is being updated from another table? So, can, and how can I write some VB code to update this table, and connect to it directly in code, as apposed to setting up a permanent connection. THe table its being updated from is called "dir check", and that updates the callers.dbf table/db. THe fields to update each other are,

    [dir check].[caller] = [callers].[hphone] and
    [dir check].[name] = [callers].[short ID]

    The below code is that was used when the database was in access 97 format




    UPDATE DISTINCTROW [dir check] INNER JOIN [callers+] ON [dir check].[short ID] = [callers+].name SET [callers+].hphone = [dir check].[caller], [callers+].name = [dir check].[short ID]
    WHERE ((([callers+].name) Is Null)) OR ((([callers+].name) Not Like [dir check].[short ID]));

    More detilas can be given to specific questions!!

Posting Permissions

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