Post reply

Using DSN-less connection link table to SQL Sever

  • March 31, 2006 at 1:06 pm

    #167546

    I have MS Access database which I want to use DSN-less connection to link the view in SQL server.  How can I do that ? Ty.

  • April 3, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • April 3, 2006 at 9:22 am

    #630451

    a quick Google search using "ms access dsnless connection to SQL Server" gave the following link in the number 2 spot:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;892490

    It appears this article should answer the question.

     

    [font="Comic Sans MS"]Vic[/font]
    www.vicrauch.com

  • April 3, 2006 at 10:46 am

    #630472

    Yes, I have tested that and using First method it works fine. Also user can connect to other SQL Servers (eg MySQL) also.

  • April 4, 2006 at 7:54 am

    #630657

    If you're interested in doing DSN-less, here's some code to get you started. Control loops are omitted for clarity.

    DoCmd.TransferDatabase acLink, "ODBC Database", strConnectString, acTable, strTableName, strTableName

    Here's one possible value for strConnectString:

    Provider=SQLOLEDB;DRIVER=SQL Server;SERVER=myservername;DATABASE=mydatabasename;Trusted_Connection=Yes

    If you have a list of tables, such as an array or a table of tables, you can loop through that once to get values for strTableName. There are lots of ways you can go at this.

    Later, if it comes time to change the connect string on your linked tables, you'll want to investigate this:

    dim dbs as dao.database

    dim tdf as dao.tabledef

    Set tdf = dbs.TableDefs(strTableName)

    tdf.Connect = strConnect

    tdf.RefreshLink

    We manage about 200 tables, regularly running against different databases (Development, Live, Testing, Training) using exactly this system.

    "DSN's!? We don't need no stinkin' DSN's!! Ha! Ha! Ha! Ha! Ha! Ha!"

    Good luck.

  • April 5, 2006 at 7:16 am

    #630980

    I actually want to link to view. Since I have employee table which contain SS and other info I want to set up in the SQL . Weekly I compared with HR data matchs with SS update and append the record. I do  not want the other people see SS. so I plan to create view for them with SS in MS ACCESS.

    I successfully link the table and view. Any suggestion for better way to deal with those issue.

    Athough I set the user have right to update, delete. The end user can't do that any more. Thanks.

    Thansk.

    Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply