SQL Server 2005 Connection

[Patrick.Grant01]

I have connected Access to SQL Server 2005 using ODBC. I am getting some very strange effects such as:

• If I view a table on a form, I will view n records that show as deleted. If I look at the data in SQL Server Management Studio its all there.
• If I try a recordset in VB, the SQL fails with a Data Type error where I assume the VB Long Data Type seems to conflict with the SQL Server BigInt..

Could this be an issue with the ODBC Connection and ADODB recordsets? Are there better connection methods that would avoid these problem?

[Tgrou]

What I could suggest is looking at a tool that will allow you to perform that migration with no errors. You could look into using an ETL tool designed of course for professional users, that can be used by non-technical users: Talend Open Studio. You can perform the migration as well as other tasks ETL tools will allow you to do.
Components have been created to link the Access database to SQL.

For more information: http://www.talend.com/

[Patrick.Grant01]

Thanks for the reply. If I use an ETL tool, I then have two copies of the same data which is more difficult to manage. I would much prefer to get the table linking working to avoid that.
Whatever the problem is, it must be specific to my PC. I am sure there must be thousands of Access Users succesfully linking to SQL Server 2005 tables. What I don't understand is why the same facility will not work for me .....