Access 2000 upsized to SQL2000
-
I have recently created a functional (forms, reports, switchboard) Access 2000 database. Due to wanting backups (and that we are mainly a sql shop), etc..., I upsized the database to SQL 2000 on a server. Multiple people in our department are able to use the db at the same time. I want to keep the MS Access front end. I chose the linked tables option during the upsize. All is/was working fine until the users requested the addition of two new columns, one in each of two of the tables. When I make the change in the local Access table, it saves. I then use the linked table manager to refresh the link in the hopes that the new column will appear in the sql table, but it does not. If I go in to the sql database and add the column, it will not save the changes.
In work around attempts I have:
At some point, I deleted the link and used some code to then re-create the link (got from HP support who searched an Access user group). However, the fields do not work with this approach.
The problem with just re-upsizing the db is that all of the data is in the sql db and not in the local db. I attempted to copy the entire access db, delete all references to the linked table, make the changes to the tables, and upsize the database (with a new name to the same server). I figured I could then dts the data from the old sql db to the new sql db. All tables dts'd except for one (the main one with the change), so this didn't work either.
Any suggestions or workarounds would be greatly appreciated.
Thanks,
Paula -
The recreate the link strategy should've worked. When you create the new link can you open the table and view the contents? If you can't then there's a connection issue, if you can then determine which Access field is having the trouble with the new dataset.
Just to clarify things, my usualy approach is:
make modification to SQL server table
using ODBC connection, recreate the link
delete the old link
drop the '1' from the recreated link name
good luck
Everett Wilson
ewilson10@yahoo.com -
Thank you for the suggestion, however, 2 things:
One, I tried to make the change in the linked table through access. It won't save any changes as it tells you that the table is linked. So, I tried to make the change in the sql database in enterprise manager. It accepted the change. So, I went in and deleted the links and re-created the links in Access. It did not like this. Now, when you attempt to make edits to the data through the access interface, it gives an error message that there is a write conflict. To fix this, I will have to go into sql through enterprise manager and change it back to the way that it was and delete then re-create the link.
After the link is made, I can access the data--so it shouldn't be a connection issue.
Paula -
Hello. I'm having trouble seeing the full set of circumstances so I'm making some assumptions here. Please let me know if I'm still off and how, some days the clouds just won't part.
The assumptions: when the changes are made on the SQL Server and a new link is made in MS Access then the table cannot be updated via the table view in Access even though all of the rows can be viewed.
If that is the case then I've often run into the problem where MS Access needs the records uniquely defined, either on the SQL server or when the link is created. Don't quote me on this but I believe MS Access use to allow linked table updates without this restriction, at least this problem seems to come up whenever I work with an older MS Access db.
Everett Wilson
ewilson10@yahoo.com -
Your assumptions are correct; however, I don't see where you are going with this. Are you asking whether primary and foreign keys are set for all tables. If so, then the answer would be yes, all tables have unique identifiers as well as foreign keys defined. My basic problem is how do I add a column to an access 2000 table once it has already been upsized. When you make the change in the local table and refresh the link through the table manager, it doesn't make the change to the sql database.
My thought for a work around was to copy the current access database, delete the links, re-upsize it with another sql db name, then dts the data from the old sql db to the new sql db. This also failed--an error in transferring relating to Not enough columns bound--whatever that means. I assume that it did not like the fact that the source table had one less column than the destination table.
So, to work around that, I copied the current production sql table and renamed it. I then added the two new fields that I am trying to add with no data. I then attempted to dts from this new test/dummy db to the newly upsized db (the one that has the new fields) so that all of the data could be placed in the newly created db that is linked to the new access db previously mentioned. However, this dts also failed. I am not sure why, though.
I am not sure what step to take next.
Paula -
Sorry, I was assuming that the new columns were being added on the SQL Server and the changes were then passed on to the Access interface. I've never tried it the other way around since I really view Access as more of the interface for the end-user and not as a tool for maintaining the data for performing extended business logic. If I was to restate my assumptions, they would be:
The assumptions: when the new columns are added to the SQL table via Enterprise Manager (or Query Analyzer or any other SQL Server based way) and a new link is made in MS Access, the table cannot be updated via the table view in Access even though all of the rows can be viewed.
Curious though, why did you try doing it with dts instead of 'Import Data'? I only mention this since 'Import Data' uses the same table structure as the dataset being imported.
Everett Wilson
ewilson10@yahoo.com -
Your assumption is correct. However, new data can be added via the access interfaces (forms or through the table view), but no data that was present can be edited. It gives a Write Conflict error. It won't let you make a change to the sql table and still work.
Isn't Import or Export the same as dts'ing? Also, I don't want the table structure to be imported, only the data. When I re-created the db in sql server with another name, it had the additional two fields, but what I'm getting the data from (the source, or current prod data) is missing the two additional columns.
Paula -
Thanks for the reply, I was more curious than anything else. Unfortunately I have no experience with Access driving the SQL Server. Good luck.
Everett Wilson
ewilson10@yahoo.com -
Hi,
You need to make the changes to the table on the server, then refresh the link in the access database.
If you want to be able to edit the actual tables, then you will have to change your database from a mdb to an adp
-
I made the changes to the sql server table (on the server), then recreated and/or refreshed the links (tried both several times). However, this causes the following problem: When a user, through the interface, attempts to edit data, I get the Write Conflict error message, and it will not save any data changes.
What is the difference between an mdb and an adp. I assume adp is access data project, but how does that affect how you access data, etc...
Thanks,
Paula -
Hi. I have the unfortunate experience of starting out as an Access person, then moving to SQL Server. Good move. Unfortunately, I ended up doing alot of side jobs for people who wanted Access front ends and SQL server backends for their system. And I unfortunately got pretty good with Access ADP's. =)
I generally create class objects (well, access class modules). From there, I call procedures using ADO. It's a bit code-intensive, but I subsequently never ran into concurrency issues when making db calls in this way.
I recommend you go into SQL server and find the procedure you're using to make the insert/or whatever the operation is. Right after the AS keyword type: SET DEFAULT ISOLATION LEVEL READ UNCOMMITTED. This is the equivalent of doing a NOLOCK table hint . You won't end up with any contention issues in this way. Just remember to write SET DEFAULT ISOLATION LEVEL READ COMMITTED at the end of your procedure (above the last GO keyword) or you'll reset the default isolation level for your connection, for the life of the connection.
I've only skimmed the contents of this thread, but I have read all of your posts, and it seems like it's just a block/deadlocking issue.
One other thing. Before you try the above, do this:
The next time this occurs, go into Query Analyzer directly in SQL server. Enter sp_who2. Take a look at the block column. The block column will identify the spid of the process causing the lock. This is just to verify if there's an actual block.
Another quick way to see if there's a block is to run a DBCC OPENTRAN in query analyzer. This will tell you if there's any open transactions anywhere on your db.
Last thing (I promise): seems like your database should be transactional. Access doesn't handle transactions (that I'm aware of ), so you've probably go alot of queries that need to be put in between BEGIN TRAN and COMMIT TRAN statements. I hope this helps, but it probably just confused. If it did, sorry. =)
-
You don't need to convert to an adp. That is a different format for an access database. This would probably cause you more problems with compatibility of current access code modules.
Basically once you upsize a table to sql, you can not add fields from within MS access. You must add then in SQL entreprise manager. If you are unable to save your new fields in enterprise mgr, check the permissions of the account you are using in eneterprise manager. Make sure the fields you use are compatibale types with MS access. Also, make sure you have one of the fields set as an IDENTITY/ PRIMARY KEY in SQL server. A key icon must show up to the left of the field in design view of the table in sql enterprise manager. If you so not have the key icon, you will not be able to update any records from access. To be able to add records you must also specify an identity increment for the primary key column so that it automatically appends the next number in the key. Next you must go to access, tools, database utilities, linked table manager and select the link table, and it will be refereshed.
Now you should be able to view the design of the table in Access and be able to update records.
-
If your problem is in updating existing data once from the Access front-end once you have added the new columns (which must be done at the SQL Server end) - then there are some constraints to the new columns that Access does not like. For example, if you added a "bit" type field in the SQL server, then refreshed the link in Access (you don't have to recreate it - just refresh it with the Linked Table Manager), it will appear Access as a Yes/No field. However, to work correctly, you must have a "default value" set (usually zero) in the SQL table design, otherwise you will get the "shared access" error in Access when you try to edit any existing record. Secondly, all existing records need to have "some" value entered into the "bit" field (0 or 1) - you will have to do this with Query Analyzer. Similar results can happen with other types of fields - you must make sure that there is a default value and that it makes sense to Access, however it most often occurs with "bit" fields.
-
You need to use an Access Project rather than a MDB(database) this will give you the capability of having the SQLserver dataend and the great option of having the Access Front. This does carry a warming of it may look like access but its much much more.
Snarlgaurd
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply