Post reply

How do I attach SQL tables to Access so I can distribute .mdb?

  • December 20, 2006 at 12:22 pm

    #170789

    I am setting up an Access front end to a SQL server database and would like to know how to attach the SQL tables to the Access database so I can distribute the Access database to 50 to 100 separate PCs.

    I know how to link tables to Access, (File/Get External Data/Link Tables.../"Files of type:" = ODBC Databases() ) but the problem is how to do it so I can distribute the Access database.

    Thanks,

  • December 20, 2006 at 1:15 pm

    #679209

    Is it an Access database or a SQL Server database? Your information says it's an Access FRONT-END and a SQL Server database. An Access front-end is the forms that are used to access the data in the SQL Server db.

    So what exactly are you really trying to do? Import the data into an Access database?

    -SQLBill

  • December 20, 2006 at 1:51 pm

    #679224

    I am LINKING the SQL Server tables to an Access database.  I used the word Attach, sorry.  SQL Server will be the back-end (data store) for the Access front-end.

  • December 20, 2006 at 2:28 pm

    #679240

    Hi Christopher

    I take it that you don't want to go around creating a DSN entry in every workstation that you deploy the database to.

    Here is quick snippet for how to link tables dynamically without using a DSN entry.

        Dim dbLocal As DAO.Database

        Dim tdfAccess As DAO.TableDef

        Dim dbSql As DAO.Database

        Dim strConnect As String

       

        strConnect = "Driver={SQL Server};Server=ServerName;Database=DatabaseName;Trusted_Connection=Yes"

       

        Set dbLocal = CurrentDb

        Set dbSql = OpenDatabase("", False, False, strConnect)

       

        Set tdfAccess = dbLocal.CreateTableDef("tblUserReport")

        tdfAccess.Connect = dbSql.Connect

        tdfAccess.SourceTableName = dbSql.TableDefs("dbo.tblUserReport").Name

        dbLocal.TableDefs.Append tdfAccess

       

        Set dbSql = Nothing

        Set dbLocal = Nothing

    Also take a look at this article

    http://www.accessmvp.com/djsteele/DSNLessLinks.html

    Roberto

     

     

  • December 21, 2006 at 9:49 am

    #679487

    What version of Access are you using?  If its 2000 or greater, why not just create an Access project and then you dont have to worry about linking tables to SQL.  I recently converted all our .MDB's to projects with SQL back end and it is much easier to maintain.

    David

  • December 21, 2006 at 11:59 am

    #679515

    Roberto,

    Thanks!  that is exactly what I was looking for.  Now, if I can only get it to work.

    Everyone else,

    Thanks for your help!  It is always nice to know there are people that have been there before that are willing to share.

     

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

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