Linked to SQL Server (Access 2000 SR1)

Topic

New Reply

We have a proprietary application that needs to update customer data from an Access database. [Proprietary] looks for tblCust from the Source.mdb file. It worked fine when tblCust was a plain old native Access table.

The problem is that the actual live data exists in a SQL Server database. So I created a View in SQLDB and linked to it from Source.mdb. The linked table is “dbo_tblCust”; so I renamed it “tblCust”.
BUT the code from [Proprietary] fails against the linked table.

I suspect the problem is: a) security permissions with the linked table; the code does not recognize the renamed table in Source.mdb (the properties of the linked table provide the wrong name?).

Let me know what you think… I am really looking for a solution to allow the [Proprietary] routine to be successful and pull data from the actual SQL Server source.

Reply | Quote

Replies

I assume you are linking to the SQL Server table using ODBC, and that you setup an ODBC data source that lets you connect to the server. In that case, and presuming you are using SQL Server integrated security on an NT/2000 network, then you will need to give each user permissions to read, edit, etc on the table in question. If standard security is being used, then you need to make sure your ODBC connection uses a login id that has permissions. Note that if you created a view, then you can set permissions on the view and not worry about the table permissions. However, you need to make sure that the view you are using is updateable – it may or may not be depending on how the view is constructed, and the version of SQL Server that is being used. Hope this gives you some leads.

Reply | Quote