May 29, 2008 at 9:43 am
Recently I set-up our Access Operational Split Database to SQL Server Express. I placed the Tables of my Operational Database in the SQL Server Express, then linked my Frontend (Queries, Forms, & Reports). Except for having to redo all the queries to dbo_Table Names everything looked fine, except that several of my Queries are now not able to update, add records.
I am a novice with SQL, and appreciate any help getting this working. Thanks
May 29, 2008 at 10:45 am
Are the underlying tables updateable (from Access)? That often becomes a problem, when the linked tables don't pick up the unique key for a given table.
Try dropping the linked tables, and relinking them. On relinking, be sure to assign an appropriate combination of columns that uniquely identify each row.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 29, 2008 at 11:24 am
Thank You, Matt. The Leave Query (the query which is not updatable) uses one table and one query. The Table is the leave table which has a Request No. - Autonumber that is the Key. The query is the Login Screen which is based on the Login table which has a ID - key. I have tried to change the Join Type without luck.
I just deleted the link then redid the link, using Phone logins as the connection. Still not updatable.
Pete
May 29, 2008 at 11:47 am
pete.trudell (5/29/2008)
Thank You, Matt. The Leave Query (the query which is not updatable) uses one table and one query. The Table is the leave table which has a Request No. - Autonumber that is the Key. The query is the Login Screen which is based on the Login table which has a ID - key. I have tried to change the Join Type without luck.I just deleted the link then redid the link, using Phone logins as the connection. Still not updatable.
Pete
Make sure that both ID's (the unique identifiers for each table) are in the query results. That can sometimes help with that.
And - both TABLES are updateable from access?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 30, 2008 at 6:09 am
Hi Pete,
I have run into this problem before.
It seems to be cause by Access picking up the wrong primary key, due to the way the indexes are named.
If you run the code below, then relink the Access tables this should solve the problem.
Declare @IndexName varchar(500)
Declare cAllIndexes Cursor for
select 'execute sp_rename ' + '''' +object_name(id) +'.' + NAME + ''''
+ ',''XX_REPL_' + object_name(id) +'''' + ',''INDEX'''
FROM SYSINDEXES WHERE NAME LIke 'index_%'
open cAllIndexes
Fetch Next from cAllIndexes into @IndexName
While @@Fetch_Status=0
begin
exec ( @IndexName )
Fetch Next from cAllIndexes into @IndexName
end
Close cAllIndexes
DeAllocate cAllIndexes
Hope this helps
Cheers
Neil
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply