Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link Tables - Read Only (Access 97)

    Hi,

    1) I've linked tables from the SQL Server to Access 97. The tables turned up to be read-only tables. Is there anyway to change it?
    2) The SQL Server data source logon box appeared whenever my program is started. What is the logon script that I supposed to add into my program in order to avoid the dialogue box?
    3) After linking the tables, the fields' datatypes from the SQL Server are different in the Access 97, why, how to sync both?

    Thanks & Regards,
    88

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Link Tables - Read Only (Access 97)

    What are the permissions granted on those tables to the SQL Server login? You can't make the tables updateable if the user doesn't have permission to write to them.

    If your sever is set up for Windows NT authentication or mixed authentication, then the login you've already done to Windows should work for SQL Server. If it wasn't set up for that, you do NOT want to bypass the dialog because it leaves your server wide open to anyone who can get into your code, and the DBA will come after you with a <img src=/S/wartgun.gif border=0 alt=wartgun width=73 height=24>.

    SQL Server tables have different datatypes than Access, and some types that seem like they should be the same (notably date fields and memo fields), aren't. When you work with SQL Server, you learn how to address those issues, but you generally do it at the SQL Server end, not in Access.

    If you explain more about what you're trying to do and ask more specific questions, you'll get better answers.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link Tables - Read Only (Access 97)

    Hi,

    Actually I've completed a program by using VB(DAO) with Access tables. I am trying to convert these tables to SQL Server. I've linked the tables from SQL Server to Access in order to avoid changes in my VB Codes. As mentioned earlier, the linked tables produced different datatypes in both environment. Any way to overcome this?

    Thanks & Regards,
    88

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Link Tables - Read Only (Access 97)

    I think you're going to have rethink that approach. Linking the SQL Server tables to Access doesn't make them Access tables, even though you can run Jet queries against them. You still have to deal with the difference in fields and behaviors, and you do that either at SQL Server or by excuting stored procedures/passthrough queries from Access.
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Link Tables - Read Only (Access 97)

    Did you use the upsizing wizard to create the SQL Server tables? It usually handles most of the problems. There are only a couple of data types that don't map correctly, though SQL Server uses different names. When you look at the linked tables in Access, they still have the same data type. An example is the Access text type which in SQL Server is varchar. What specific types are you having trouble with?
    Wendell

Posting Permissions

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