Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Relinking 2 SQL Server (Access 2000)

    I am developing an app that has a SQL Server back end, and Access front end. There are only ten tables in the SQL backend to which I link. As I develop and modify it, I move the mdb file from my workstation to another the weirdest thing is occurring. When I move it and open it from the other workstation, I bring it up with the shift key down, and then relink to the nine tables.

    After relinking, eight of the ten tables open in "Read Only" mode. Two allow editing and adding records. I end up having to delete the eight tables. Then "File", "Get External Data", "Link Tables" adding the eight tables, then renaming them in order to delete the "dbo_".

    I cannot find a pattern, or any difference in file structures between the two and eight. Any ideas why some do and some don't? Is there a better way to develop an app using SQL Server on one machine and moving to another for deployment?

    Thanks in advance for your ideas!

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relinking 2 SQL Server (Access 2000)

    Does each of the SQL Server tables have a unique index? Without that, the linked tables in Access will be read-only.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Relinking 2 SQL Server (Access 2000)

    Yes, 6 of the 8 have primary keys. All of the tables were originally in Access and coverted to SQL Server using the upsizing wizard. I have assumed all this time that the autonumbered fields that were primary keys in Access were maintained as unique during the upsizing process.

    Where would I look to ensure that they are in fact still unique?

    Thanks!
    Ken

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relinking 2 SQL Server (Access 2000)

    I don't know anything about SQL Server, but I assume that there is some kind of management tool to view and edit the design of tables, views etc.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Relinking 2 SQL Server (Access 2000)

    Hans,

    Thanks. I did take a detailed look at the tables, and there is a field that is unique, and is incremented automatically upon adding new records. And each of the tables previously mentioned do have a unique field automatically incremented and set to stay unique.

    thanks again for your time and consideration.

    Ken

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relinking 2 SQL Server (Access 2000)

    But is there an index on the unique field?

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Relinking 2 SQL Server (Access 2000)

    Hans,

    You are AWESOME! For someone that doesn't "know anything about SQL Server", you sure hit the nail on the head with that one.

    THANKS!

    Ken

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relinking 2 SQL Server (Access 2000)

    here is a little code that I thought you could use if your ever have lots of SQL tables to rename. It removes the dbo_ from the linked table name -
    Dim obj As AccessObject
    For Each obj In CurrentData.AllTables
    If Left(obj.Name, 4) = "dbo_" Then
    DoCmd.Rename Mid(obj.Name, 5), acTable, obj.Name
    End If
    Next obj
    Set obj = Nothing

Posting Permissions

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