Link Tables – Read Only (Access 97)

Topic

New Reply

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

Reply | Quote

Replies

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 .

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote