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.
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.
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.
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.
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.
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.