Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2009
    Posts
    10

    Unanswered: TransferDatabase problem

    I have been successful in setting up links to SQL tables on the fly (based on the project people are working with) using the following code:

    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN = Projects;" & "Database=Projects", acTable, "E11235Loc", "E11235Loc"

    The problem is the 'Select Data Source' window pops up asking the user to select the DSN. Even though it already knows the dsn and totally ignores that extra input (I know that because you can click on the wrong dsn and it still connects using the correct one).

    How do I get it to skip opening the 'Select Data Source' window? Or if it has to open, blow by it without requiring user input?

    I'm using Access 2007 front end with SQL 2008 backend. OS is Windows server 2008.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Is there a specific reason you need to connect to the SQL tables on the fly? I usually just link the tables into MSAccess and then design code to select the specific table.

    Otherwise, perhaps these examples may help in some way:

    http://www.dbforums.com/6274875-post31.html

    http://www.dbforums.com/6282465-post37.html

    http://www.dbforums.com/6274793-post22.html
    Last edited by pkstormy; 11-23-09 at 23:29.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2009
    Posts
    10

    Still Looking

    Thanks for the quick reply. I checked out the links and it's not really dsn problems I'm having. I can't set up the link ahead of time as the SQL table is dynamically created when they start a new project and then I try to make the link. Hoping I can find a way to make the whole process work behind the scenes.

    It's the little things like this that seem to waste our time. I was so excited when I got the tables to create, got them to format, index, and everything on the fly. Now the link is causing problems. I have done this dynamic linking in previous versions of Access without problems.

    I do think this pop up that asks for the dsn connection is a bug in Access . . . as I mentioned earlier, it doesn't matter what you click on in the popup window as it already knows all the information it needs from the code. Was just hoping someone else had experienced it and found a work-around.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There is an ODBC Fix in the last link in the last post. I've noticed that sometimes (often) the Registry get's confused about the already established DSN's (and 'possibly' a reason you'd get the prompt). If you went into the Control Panel->Administratrive Tools->ODBC Manager and then looked at the ODBC system DSN's created (that you know you created), sometimes they are not listed. This is a bug. The last link has a fix for this bug. Perhaps it will help, perhaps not. I just hate it when I create an ODBC DSN (system) and then I have to edit the Registry to 'reset' the registry line which makes the ODBC DSN's mysteriously dissappear. Once I re-set the value in the registry, the ODBC DSN then shows up and it then recognizes the DSN.

    Another thing I can think of, is that if ANY ODBC DSN's are named slightly different (ie. NOT EXACTLY the SAME for EVERY computer - ie. 1 is spelled slighly different), this would OFTEN cause the prompt. You may want to check that ALL ODBC DSN's which reference the same SQL db are named EXACTLY the same. This drove me crazy for many months until I found a DSN on 1 user's computer which had a slightly spelled different name to utilize the same SQL db. (ie. it worked one time, then when someone got in with the slightly different DSN, it 'broke' the others and they would get the prompt, once refreshed on their computer, it would then 'break' the other DSNs, refreshing that then 'broke' the other connections, repeat, repeat, etc....Again, I kept wondering why I got the prompt until I found a DSN misspelled differently on 1 computer. Since you do this in code, it may again not help (but I want to give you all the info I can think of.)

    Again, Not sure if this helps. It's the only reason I can think of that you would get the 'Select Data Source' problem. (other than a possible MDAC problem). You may want to also look at possibly re-installing MDAC since that's what controls ODBC DSN's.

    There is also another checkbox called: 'Prompt for a location each time' but again, I'm throwing some things out there that I know of that I've come across but I doubt this is the cause of your situation.

    As a last note: if you're creating tables on the fly in SQL Server, perhaps refreshing the ODBC on startup (as in one of the previous links) may help. You may want to give it a try since it sounds like the ODBC DSN isn't taking into account the new tables. I will sometimes need to refresh the ODBC DSN to account for new tables.
    Last edited by pkstormy; 11-24-09 at 09:57.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you 're-create' the DSN on that computer and then run the code, does it then work? If it does (but then breaks for another user), I'd guess that the previous post might be the problem.


    FYI: The registry key for ODBC DSN's is: HKey_Local_Machine\Software\ODBC\ODBC Data Sources. If you delete the (default) key (it's ok to do this - it re-creates itself automatically and the DSN's suddenly then appear in the ODBC Data Sources in Administrative tools), you'll notice your DSN's suddenly then appear in the Control Panel->Administrative Tools->ODBC Data Sources.

    Again, it only takes 1 misspelled DSN to cause the problem (but will still connect.)
    Last edited by pkstormy; 11-24-09 at 09:49.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Otherwise I might try a File DSN versus a system or other DSN. I've had luck using File DSN's (as long as user's can access the location of the File DSN.)

    Are you using a File DSN, User DSN, or System DSN for the 'Projects' DSN you created?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    A DSN is nothing more than a file that stores extra parameters for a connection. I would do a search for DSN Less Connection SQL Server and see if you can get the right string of parameters. I think the best thing to do is to manually link to the table(s) that you want in SQL Server. Then, in Access, design the table. You will get a warning message, but then you will see all of the fields. If you right click anywhere in the white space and choose Properties from the context menu, you should see the connection string it is using in the Description property. I think (not 100% sure) that if you copy and paste that string into you TransferDatabase command it will be DSN Less.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding DSN versus DSN-Less (even though you mentioned this isn't your problem but you may want to look at a dsn-less connection)...

    If your intent is to use DCKunkle's advice on DSN-Less connection (which I personally don't like to use except for php scripting for the web - although I do use dsn-less when dealing with large recordsets. There's pros and cons to both ways although the dsn-less is slightly faster since it doesn't need to tap into the registry to get the parameters. As DCKunkle pointed out, a DSN simply holds the parameters for the connection in the registry (I call it the gate-keeper) whereas with a dsn-less, you supply all the parameters in your code. Again, there's benefits to both ways and I personally don't like to maintain the code or hard-code usernames/passwords and prefer to just let the DSN handle everything (as mentioned in my previous post - I use vba code which automatically creates/refreshes the ODBC DSN when the mdb is opened since it is a pain creating the DSN on everyone's computer - a drawback for using DSN.) But here some links that may help you decide which is the best approach for you if you're interested:

    DSN vs DSN less Database Connections

    or here:
    http://www.accessmvp.com/DJSteele/DSNLessLinks.html

    or
    http://support.microsoft.com/kb/892490 or http://support.microsoft.com/kb/165866

    or
    http://www.granite.ab.ca/access/dsnlessconnections.htm (pros on using DSN-less connection)

    or (for interesting reading):
    http://www.4guysfromrolla.com/webtech/070399-1.shtml

    Regarding your specific issue, I almost wonder though if you're not dealing with a time-out issue since this 'might' be a factor. I can't say for sure though since I don't programmatically link to the tables as your code does but there is a time-out limit. I link the tables into my frontend and let my frontend code (or SQL Server) handle permissions to the specific tables.

    You may also want to check though to see if the DSN shows up in the Data Sources ODBC Administrator. It's possible that it can't parse the correct DSN. I've had a similar (although not exactly the same issue), when the DSN's didn't show up in the Data Sources ODBC Administrator (even though they did exist in the registry.) After I ran the fix as mentioned, the problem went away.
    Last edited by pkstormy; 11-24-09 at 21:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Mar 2009
    Posts
    10

    where o where?

    This is a treasure chest of information! I've been on another task for awhile and now starting to wade through all these great suggestions. Of course I want to try the easiest stuff first. The comment by pkstormy, 'check box for Prompt for a location each time' is eluding me. I have looked for that check box in Access Options as well as the ODBC administrator but cannot find it. Anyone have clues on how to get to it?

  10. #10
    Join Date
    Mar 2009
    Posts
    10
    now I'm trying out a DSN-less connection by using:

    DoCmd.TransferDatabase acLink, "ODBC Database", "Data Source=ENG1\ENG;" & "Database=Projects", acTable, , "E11235Loc", "E11235Loc"

    but I get the error, 'could not find installable ISAM'.
    Can anyone tell if this is still my code problem or if maybe it would work if . . . How do I check out the other problem without doing any permanent damage? I attempted to wade thru the Microsoft site, You receive a "Could not find installable ISAM" error message or some file types are missing when you import files, export files, or link files in Access 2000 but don't see what to use for this SQL database.

Tags for this Thread

Posting Permissions

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