Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    4

    Unanswered: Link Table to SQL Server 2000

    I have an Access application running on Microsoft Windows 2000.
    As the data gets bigger, the performance appears to be dramatically reduces.
    I am trying to migrate to SQL Server 2000. I have migrated the database, create link table from access to SQL Server so that I do not have to change my application.
    However, it can't seem to retrieve the data at all.

    Has anyone experienced the same issue?

    Thanks,
    Citro

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There could be lots of reasons for this.

    What do you see when you open a linked table? An empty data set? An error message? We're going to need more info to help you out.

    In the meantime, be aware that migrating your database to SQL Server will likely result in a large improvement in performance, but you may not get the best performance unless you also migrate your stored Access queries, creating them as views or stored procedures on your SQL Server database. The idea is to ensure that the maximum amount of processing occurs on the server, and while recent versions of Access attempt to pawn off as much as possible onto the server, they cannot always do so.

    Before you do a lot more development on this, check out Access Data Project interfaces (they use the ".adp" extension). You can move your existing forms and modules into an adp file, and it acts as a shell into the server database. This would give you the best performance.

    blindman

  3. #3
    Join Date
    Aug 2003
    Posts
    4
    Thanks Blindman for the reply

    At this stage, I am not migrating the whole application to SQL Server yet.. I am just migrating the database. When I open the linked table, I can see exactly the same as if I open the access table. There is no error whatsoever. But when i open it using the current Access application, it does not retrieve any data at all... empty.. no error however.

    Look forward to your opinion.

    Cheers,
    Citro

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm still confused. Do you mean that when you open the table in SQL Server you can see the data, but when you open the table through Microsoft Access you see all the column headers, but no data?

    blindman

  5. #5
    Join Date
    Aug 2003
    Posts
    4
    Sorry to confuse you.

    When I open the migrated table in SQL Server, the data is good.
    When I open the link table from Access Linked Table (Linked to the migrated table in SQL Server), the data is also good...no error whatsoever.

    But if I use the Access application to retrieve the data, it shows nothing.

    Cheers,
    Citro

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you can see data when you open the linked table through the Access Tables tab, then all of your connections are correct. That means the problem is in your form(s), or the queries they are based on. They are filtering the data in some manner so that it is all excluded.

    What are the data sources for your forms? Are they based directly on the tables, stored queries, or form-specific SQL strings?

    Check your form properties, under the Data tab, and make sure there is no filter set.

    blindman

  7. #7
    Join Date
    Aug 2003
    Location
    Toronto, Canada
    Posts
    10
    Maybe this is too obvious, but when you link tables from SQL Server the table names are different by default. Are you sure that you checked all of your forms and queries for correct table names? Have you tried creating a new form bound to a linked table with maybe only one bound control. That might help you isolate it down further.

Posting Permissions

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