Linking Access to SQL Server

Hi,

I have an existing Access DB with many Tables, Forms, Queries and Reports. I need to put a SQL Server Back end on this database.

Do I need to code a mirror images of these objects or is there some way to export these into SQL Server?

Also, where do I deal with assigning usernames and passwords? Access or SQL Server. I need to have different views for different users based upon their rights (user, admin)

Thanks,

Mark

There is a full upsizing process that will handle all this for you. Read more here:

http://www.microsoft.com/sql/solutions/migration/access/default.mspx

|||

Hi Mark:

I am not sure if I understand your questions entirely.

If you are looking a way to convert your access database to sql database, following is the link to MSDN article that may help:

http://msdn2.microsoft.com/en-us/library/aa164792(office.10).aspx

About second part of your questions (usernames and password). Could you please provide more information?

Thanks.

Linking a SQL Server 2000 DB to 2005

I am trying to link an existing SQL 2000 server to a new 2005 box, but for some reason it will not link. I tried another 2000 server and that one worked perfectly well.

When I link it and perform a simple query...
select a, b from [server1].[db1].[dbo].tablec

I receive a 7314 message with text "OLE DB provider "SQLNCLI" for linked server "server1" does not contain the table "].[db1].[dbo].tablec". The table either does not exist or the current user does not have permissions on that table.

What's odd is that I was able to link the problematic server to other SQL 2000 servers with no previous issues.

Any ideas?

Thanks,
SteveHiya Steve...

Let me start by asking the obvious...have you verified that the table [server1].[db1].[dbo].tablec actually exists on the 2005 server? Check for typing errors here, is what I am getting at.

Also, in Enterprise manager, can you see the target table when you expand the linked server icon and click on the "Tables" icon?

Have you verified that the login information you set up when you created the linked server DOES have permissions on the target database/table?|||Paul,
The table that I'm trying to query is on the SQL 2000 server. I'm running the query from the 2005 box. I probably wrote my initial post incorrectly. But, to answer your question, the table does exist.

The login information that I provided on the 2005 server does have permissions on the target database/table.

Not to sound dumb, but can you view the tables on the linked server from 2005 like you could in 2000?

Thanks for the response,
Steve|||Now I'm outta my league, Steve. Done lots of linking to remote servers/databases by various means, but only on SQL Server 2000.

2005 is still sitting in my desk drawer being ignored until the boss decides other have bled enough against the leading edge.

Linked Table to Access

I've created a linked table to Access. I want to be able to delete records and modify existing records through this linked table. I used the account with sa privelleges yet i am unable to delete/update any records.
Does anyone know if there is any fix or am i doing anything wrong ?Are you getting any sort of error messages?|||Try going into SQL Server under Roles for that database. Click on the Public and make sure all the user created tables are checked in the rows for all the columns (green checkmark). Also make sure you have permissions which is sounds like you do as well as any other users. Maybe add your login account to the Security and then also to the database Users. It should default to the Public role as permissions. Did you set SQL Server up with Mixed Authentication?|||Also check that you have unique indexes\ PKs set up for the table. Access won't edit data in a linked table without a candidate key field(s). It will draw this from the BE or request this from you when you link if such a constraint isn't defined in the BE. If you don't specify these fields in the latter instance then Access will link to the data but you get read only acccess.

HTH