Uploaded Access Table to SQL Issue 2

[wsproperties]

I used the upsizing wizard to upload an access table to sql. Lets call the table mytable1. I set permissions to alter, delete, insert, select, and update. I went back into MS Access, I chose File, Get External Data, Link Table. I then linked the mytable1 and brought it in as a table. I went into the table and tried to make a change. I am not able to make a change at all. In addition when I attempt to log in using my own log in, I cannot do so. I can log in using the system log in. Any thoughts and solutions here.

 

[CP60]

I think you can only Select (maybe also Update).
To Update, Delete and Add you should use the sql driver and do it in code off a connection, and use best stored procedures with parameters.

 

[RiverGuy]

I've never used the upsizing wizard in Access. However I do remember issues years back with linked tables to SQL requiring a primary key on the SQL table to perform deletes. You might check this.

 

[SQLSister]

I agree with River guy, the most likely culprit is the lack of a unique identifier (most commonly a primary key) on the table.

The login issue is differnt and I guess would depend on your own permissions and Access's permissions. Woul dneed more info to figure out what is happening there. Are you using SQL logins? are you storing logins in a table and using that to determine permissions through application code? How is access connecting to the datbase? Who are the owners of the tables?

Since you used the wizard, I would go back and carefully check you r datatypes as well. The upsizing wizard often chooses very poor datatype conversions.

"NOTHING is more important in a database than integrity." ESquared

 

[wsproperties]

As a piggy back, is it possible to create a new query in access by creating a field name, populating the field name with sql data in a table on your server, also including blank fields or fields with no populated data then upsizing it into sql to create a new table. The key is to establish a primary key. Or would I have to create the table directly in sql and somehow link the table I want to populate already in sql to that particular table.

 

[JoeAtWork]

Just modify the table in SQL Server, then refresh the link in Access (in the Linked Table Manager).

Joe Schwarz
Custom Software Developer

http://www.plandev.net