Recently had to migrate the back end of a number of Access databases to SQL. I've managed to do the hard bit which was recreating the reporting macro's and modules, however as the database was badly designed to begin with I have a view that "unions" the data in a normalised form for general use.
When all was in Access there was a delete query that would flush the "buffer/reporting" table I pulled the info into and re-populated. Now the back end is in SQL I can't for the life of me get a delete query to work. I can delete from the SQL server obviously, but when I attempt to run a Delete query I simply get a "Records were not deleted, Data is read only" error message.
What am I doing wrong? I'm not a SQL buff so am not quite sure what to be looking for if it's there.
How did you recreate your tables in SQL Server? Did you import the Access tables? Or did you design new tables in SQL Server?
I think it is a matter of missing primary keys. Do all your tables have a Primary Key? If so what kind of data type is your Primary Keys? When I moved my Access data to SQL server I ended up picking a data type in SQL Server that Access couldn't handle. I think I chose BigInt for my keys and Access does not have an Integer value that large so when Access retrieves the data it cannot retrieve the Primary Key so you cannot delete anything from the table. Int is comparable to Access' Long. If you have any BigInt primary keys, change them to Ints and it should work.
Right, all the tables have primary keys apart from this specific table. This table is grabbing3 columns from another table and "union"ing them and thus the former primary key from the host table now becomes non-unique - hence the lack of a primary key.
Sounds like a Stored Procedure would work. Create a stored procedure in SQL Server with the following SQL:
DELETE FROM [your table name]
Then create an ADO connection to the SQL Server instance and use the Execute method of the connection to run your stored procedure. Without a primary key Access won't be able to do the delete so SQL Server needs to do it.
Thanks so much for your help. I've managed to successfully migrate access and it's nonsense to SQL and have even created a pass through query in Access to run the stored procedure - I'll frequent this establishment again.