Results 1 to 6 of 6
  1. #1
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78

    Dsn or dsn less which one i have to use

    I have some misunderstanding about dsn and dsn less



    1- when at first step i upsize access to sql server with access 2010 it uses dsn or dsn less?

    2- when we upsize access is necessary doing that with dsn, i read some where for distribution front-end file they can use dsn less, i mean at first for making a upsized file with linked tables to sql server i must use dsn?

    3- after make a upsized file with linked table to sql server (windows authentication) , if i want to distribute this file to other users in the domain (windows authentication) without installing dsn on each machine what i have to do? I have seen they use vba code but i need a step by step procedure, i mean this vba code when will be executed? In which event or macro?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    1) Way back when I upsized from Access, I used a dsn. I don't know if using the SSMA would require a dsn, but I'd doubt it. I normally create directly in SQL Server now.

    2) Again, I don't think it's a "must", but it's probably common. I use dsn-less now

    3) You can use dsn-less, or you can create a dsn on the SQL Server, and link using that. That's what I did prior to using dsn-less. I'm not sure one is better than the other, though to link to new tables now all I do is add them to a local table. If you use code, it would need to be executed before you need any data. I have it in the open event of whatever form opens first.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78
    Is it possible send the code you are using for dsn less connection

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I adapted this:

    https://support.microsoft.com/en-us/kb/892490

    and use a local table with server, database, and table names (most of my apps connect to several different databases). There's also this very popular method:

    http://www.accessmvp.com/djsteele/DSNLessLinks.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78
    What i understood The adapted code you use make linked tables from the table you define for table names and it does not need pre defined linked tables

    And second code just chech and change connection string of linked tables, it means for running second code we must have a database with linked tables to sql server , so we must use ssma assistant for creating these linked tables and then run the second code

    but first one does not need pre defined linked tables just according to table names there are in sql server it maked it self linked tables

    My understanding is correct paul?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Correct, the first does not need existing linked tables. In my code I delete it if it exists. I open a recordset on my local table and loop through it, calling that function for each table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums