Results 1 to 7 of 7

Thread: Access 2003 db

  1. #1
    Join Date
    Mar 2013
    Posts
    4

    Unanswered: Access 2003 db

    Hi , i have inherited a splitted access 2003 database on a local pc and l am required to move the backed to a shared drive and create a network for the team. i need help with moving the backend and is there any simple way of getting the frontend installed on the other workstations and connect to the backend.
    Richard

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Move the BE on a network share and give access to this share to all users of the FE.
    2. Open the FE and use the linked tables manager to reattach the tables (if DSN-less attached tables are used) or create/change the ODBC connection on each client (if the tables are linked through a DSN, using the ODBC manager). If the FE does not use attached tables, change the connection string(s) to the data.
    3. Copy the FE on each client.
    4. Open the FE on several clients (all ideally) and check that everything works as expected.
    Have a nice day!

  3. #3
    Join Date
    Mar 2013
    Posts
    4

    Mr

    Many thanks Sinndho

    1. is there a way to check if Dsn is being used.

    2. Is there a way of copying the FE onto the other PCs by a link or it has to be done individually by Usb stick.

    3. Finally l know it sounds funny but l have difficulty in moving the BE to the shared drive. The copy command doesn't seem to copy all the data across. Do l use the Backup Command

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. You can examine the Connect property of the Tables:
    Code:
    Sub TestConnect()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs
            If Len(tdf.Connect) > 0 Then
                Debug.Print tdf.Name & ":", tdf.Connect
            End If
        Next tdf
        Set dbs = Nothing
        
    End Sub
    Here's what I got from a sample database:
    Clients: ODBC;DRIVER={SQL Server};SERVER=BSMM-BRAVO;DATABASE=Sales;Trusted_Connection=Yes; <-- DSN-Less connection
    dbo_Clients: ODBC;DSN=Sales;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=Sales <-- DSN connection
    2. You can create a share on the server and store the master copy of the FE there. You give access to this share to all users needing to copy the FE. There are several techniques that allow to automate the process. See in the code bank: http://www.dbforums.com/microsoft-ac...code-bank.html

    3. There should be no problem, as the BE is just a file. Be sure that no FE is connected when you make the copy, though.
    Have a nice day!

  5. #5
    Join Date
    Mar 2013
    Posts
    4

    Mr

    Many thanks Sinndro.
    Can u suggest any book / mat that is helpful in setting client-server for access and access / sql.. l a am new to this concept.

    2.i understand migrating de BE onto a sql server will give performance. I have managed to up size a copy of Be onto sql 2008 server. How do l get the FE talk to de Sql Backendm

    Many thanks for the assistance

    It

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are a serious developer in Access that I'd strongly recommend getting one of the Sybex developers handbooks. even though its not been updated in a while and costs a lot its a fantastic resource
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Sybex has indeed very good books. If you can still find it, I also recommend "Hitchhiker's guide to Visual Basic & SQL Server" (William R. Vaughn, Microsoft Press ISBN 1-57231-567-9). It's rather old but contains a lot of useful info.

    2. The easiest way to migrate the data to a SQL server is by using a wizard, either in Access (database tools) or in SSMS (SQL Server Management Studio). If you want to do this manually, you must create tables in a database on the server with the same structures as those residing in the BE. Then you create linked table to the server in the FE, transfer the data using INSERT queries (from the BE to the linked tables (i.e. to the SQL server database), then you disconnect the FE from the BE. Always keep a backup of both the FE and the BE before beginning the migration process.

    When you're familiar with SQL Server, you can consider using stored procedures to exchange data between the FE and the SQL Server and drop the attached (linked) tables.
    Have a nice day!

Posting Permissions

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