Results 1 to 5 of 5

Thread: ODBC issues

  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Unanswered: ODBC issues

    july was a great month for mysterious errors!
    here is the last one waiting to get fixed. it would be great if you can point me in some useful direction(s)

    application is access frontend looking at SQL-Server 2k backend.
    there are no linked tables, just pass-thru queries and sprocs.
    there are 60 users.
    the system has been alive and well for four years.
    there are a dozen semi-static lookup tables that are copied down to the frontend to feed combos.
    these semi-statics change slowly - maybe one change in one table each week or two - but the lookups are crucial to the application.
    datetimes in a 'status' table in the backend and frontend are compared regularly to see if one or more of the semi-statics needs updating at the frontend.

    the frontend update process is:
    delete the frontend lookup table
    copy down the server lookup table with
    Code:
    DoCmd.TransferDatabase acImport, "ODBC Database", gloStrConn, acTable, serverFile, localFile
    i just added the 61st user - and .transferdatabase refuses to work for her!

    it is throwing a 2507 which is something like 'unrecognised database format'.
    the lookup doesn't arrive at the frontend.
    the application goes sick!

    this user's MDAC is older than mine but is the same rev used by dozens of other happy users.

    ODBC seems to be mostly alive (many other SELECT and UPDATE and INSERT queries and EXEC sproc work perfectly for this 61st user) just .transferdatabase fails.

    connection is DSN-less as you can see: gloStrConn is correctly populated at the .transferdatabase call

    user is running Japanese-XP/O2k3

    any ideas what might be causing this mess?

    izy
    currently using SS 2008R2

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Hey Izy,

    It sounds like your distrubiting the front-end somehow to each user or am I wrong and it's 1 front-end used by multiple users. To help narrow the problem down, if you were to create a button on a form, have the user click on the button and try doing a .transferspreadsheet (verses .transferDatabase) against an excel file which might be local on the front-end side, would that help troubleshoot it?

    Is there any part of the .transferdatabase variables (ie.. gloStrConn, serverfile, or localfile) which might be used anywhere else which do work so you can narrow down which variable, if any of them, that might be giving you problems? I take it you have other users running Japense XP/O2k3 where it does work or not? (I couldn't tell). Does this seem like an operating system problem, an ODBC problem for that specific computer, or possibly a specific user problem for just that table on the SQL Server side (does it work if you could get a 62nd user to try it on the same computer)? Is that 61st user able to go to another computer, try it and will it work? When was the 60th user added (was it recently?) I was a little confused if you were narrowing it down to a 61st user problem or a computer problem on that user's machine. Does that user possibly have 2 versions of MSAccess on the computer and it's getting confused on which version?

    I'm not sure if you can test out any of the above but I thought I'd throw out a few troubleshooting ideas.
    Last edited by pkstormy; 08-04-07 at 13:37.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thanks Paul,

    by now i have tried all of that and much much more - zilch! given that this app works under many different localised versions of Win/Off/ODBC i decided to blame the Japanese version for this issue so i started browsing Japanese-language VBA & Access sites. this was not a joyous experience since i don't know anything at all about the Japanese language, but guess what: inspiration!

    my method is
    .TransferDatabase acImport, "ODBC Database", blahblahblahblah

    what i see on Japanese sites is
    .TransferDatabase acImport, "ODBC データベース", blahblahblahblah

    observations:
    a/ YES, this site does support such characters and my machine can read them - if your machine cant read them, see attached .GIF
    b/ google translates 'データベース' to 'Database'
    whatever was redmond thinking of when they did this in the Japanese version???
    c/ the method
    .TransferDatabase acImport, "ODBC", blahblahblahblah
    seems to work just as well in English versions. i'm still waiting for Japan to come back with testing results, but i'm cautiously optimistic.

    question:
    is anyone out there still using ODBC for this sort of stuff, and if yes,
    which syntax: "ODBC" or "ODBC Database" ?

    supplementary question:
    why do both variants exist?

    izy
    Last edited by izyrider; 02-05-08 at 14:26.
    currently using SS 2008R2

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Hey Izy,

    Sounds like this is a handful. Since you're one of the more advanced users, I figured you had already checked everything I related and I was hoping I might have hit on something forgotten.

    Personally, I'm glad I don't need to deal with this (Japanese) issue. Thanks for the info though - it's something I'll keep in the back of my mind. I do use ODBC quite a bit.

    I can't answer your question though on why both variants exist. I'll do some looking myself.

    Good luck!
    Last edited by pkstormy; 08-12-07 at 12:42.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Paul,

    i meant it when i said '...by now' (as in today)
    that's not the same thing as 'boring, history, yawn, obviously done that already'

    i followed several of your suspects that i had earlier 'assumed were ok because i never code bugs '

    thanks, izy
    currently using SS 2008R2

Posting Permissions

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