Linked Tables - After Path Updated Can't Add Records
Hello-
I created a MS Access 2002 database with linked tables on a SQL Server
database by way of a File DSN, A. I have created a form which points to
one of the linked tables. After I finished testing it in the
development environment I updated the path for all the linked tables to
a new File DSN, B. After this action, I opened the form to test adding
a new record and the "add new record" navigation button has been
disabled. Can anyone help? I need to be able to add new records
through the form and can't figure out why I am now NOT able to add a
record. PLEASE HELP!!
Thank you,
nosenia
--
nosenia
Posted via http://dbforums.com
View Complete Forum Thread with Replies
Related Forum Messages:
Linked Reports Not Being Updated When Master Report Is Updated
Since updating to SQL Server 2005 SP2 I've noticed two things about Linked Reports. 1. I do a lot of 'Snapshot' reports. With SP-1 if I updated a master report and made any changes to the Parameter List - it undid all my custom parameter changes on linked versions (restored to the Master Reports Defaults). While this is no longer happening with SP2 - it is still 'unhiding' the parameters. 2. With SP-1 if I added/deleted columns or made other changes to the report structure - the linked reports would pick up on the changes with their next refresh. With SP-2 I'm finding that I have to 'Re-link' the linked report back to the master report before the changes are refreshed. This is very time consuming especially with each report having 8 or more Snapshot reports pre-set up. Am I missing something - or is this a 'bug'... Any help would be appreciated...
View Replies !
How To Save &&"New&&" / &&"Updated&&" Records Only In To Destination Tables
Hi I have a requirement like, i need to save all the records from my Flat File on Monthly basis to my database table. In the next month, the flat file may be added with 10-20 records and also some updates happened to my old records. Though the faltfile is same for each month, the changes are occured in some records and also added with few records. So when i am loading this data in to Database table, i need to just update the changed records and also needs to add the new records. I don't need to touch the remaining records. How can i do that in SSIS 2005 using different data flow tasks ? Thanks Kumaran
View Replies !
Get Last Updated Records?
If I update a recordset a group of records using dynamic SQL where I update the TOP n records, is it possible to get the set of records that was updated? CREATE PROCEDURE usp_Structural_ScheduleComponent @cProject char(7), @cComponentID char(10), @iPour int, @iQuantity int, @iAvailable int OUTPUT, @dtCast datetime OUTPUT AS SET @dtCast = convert(char(10), getdate(), 120) DECLARE @cSql varchar(500) SET @cSql = 'UPDATE tbStructuralComponentSchedule SET PourNumber = ' + CAST (@iPour AS VARCHAR) + ', ScheduledDate = ' + '''' + CAST(@dtCast AS VARCHAR) + '''' + ' WHERE EntryID IN ( SELECT TOP ' + CAST(@iQuantity AS VARCHAR) + ' FROM tbStructuralComponentSchedule ' + ' WHERE fkProjectNumber = ' + '''' + @cProject + '''' + ' AND fkComponentID = ' + '''' + @cComponentID + '''' + ' AND IssueDate IS NOT NULL' + ' AND ScheduledDate IS NULL' + ' ORDER BY EntryID DESC)' EXEC(@cSql) IF(@@ERROR <> 0 OR @@ROWCOUNT < = 0) RAISERROR('Failed to add components to pour!',16,1) SELECT @iAvailable = SUM(CASE WHEN IssueDate IS NOT NULL AND ScheduledDate IS NULL THEN 1 ELSE 0 END) FROM tbStructuralComponentSchedule WHERE fkProjectNumber = @cProject AND fkComponentID = @cComponentID GO -- Is there a way to return the recordset that were modified in the update? Mike B
View Replies !
Big Records Not Always Updated
In our database we have a memo field that sometimes coud be over 9 MBytes. We are using the following code (VB) to update it lReg = Len(fielText) lOffset = 0 Do While lOffset < lReg sChunk = Left(Right(fieldText, lReg - lOffset), 32768) rs("document").AppendChunk sChunk lOffset = lOffset + 32768 DoEvents Loop rs.Update The problem is that sometimes the record is NOT updated and there is NO errors returned by SQLServer. Do you have any suggestion? Is there another way to update records with big fields?
View Replies !
Config File Path Not Being Updated In The Dtsx File While Deployment
Hello All, I experienced a weird error while deploying my SSIS package. After running the manifest file, i noticed that one of the configuration file's path was not updated in the dtsx file. My solution has 8 packages and almost every package has 2 configuration files. Except 1 file every other config file's path is being updated. Has anybody experieced such a problem? Thank you in advance for your help Sumesh
View Replies !
Need To Track Updated Records
Hi all, I have a data of applicants. Everyday we dump this data into SQL server. Now I need to generate reports everyday so that we can track how many records get updated everyday. Now the thing is that the applicants are in various stages. So my reports need to track the how many applicants changed from stage "abc" to "pqr" and how many changed from "pqr" to "xyz". Now it is not necessary that all the records change stages everyday. thanks in advance, Rohit
View Replies !
How Can I Report On Entered And Updated Records
My database has many table, each table has a DateEntered (datetime), EnteredBy (nvarchar(50), LastUpdate (datetime), and LastUpdateBy (nvarachar(50). Is there an easy (ha) way to pull a list of the records that were entered and/or updated for a date range. Hopefully without a select for each table. Maybe a tool someone knows of?
View Replies !
Protecting Records From Being Updated And Deleted
Hi I am using sql server 2005 express and would like to keep all my fields from being both updated and deleted. In other words, once I create a new record, I would like to have it protected from being deleted and I dont want the field values to be updated/changed from the values initially entered. Is there a way to this without running triggers or changing database permissions and user roles? I tried making the database read-only, but then of course i cant add new records. Thanks
View Replies !
Updated Records From A Table Insert Into Another
Hi: I need to create a trigger to save every record which has been updated in a table (e.g. "Contacts") into another (e.g. "Shadow_contacts). So basically something like a versioning on database level. I have no experience with it and any precise hints/solutions would be very appreciated selmahan
View Replies !
Linked Server To Excel With UNC Path
We have been having problems getting a linked server to an excel file working with an UNC path. If the UNC path is to the SQL server itself it will work but not if the UNC path is to another server. The SQL server 2000 SP 4 processes are running under a domain id and we can logon interactively with that same login id and we can access the excel file via the same UNC path. We have tried it with setting up a linked server and also linking to it 'on the fly': SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="\AnotherServerUNCpathexcel.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1$ go The message we are getting is the following: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error. OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.]. It appears from reading Microsoft's documentation and other topics that this should be possible. Any ideas on what we are missing?
View Replies !
UPDATE Stored Proc -- Updated Over All Of My Records
I wrote a stored proc to be implemented in a datagrid. I went and used it on a record to update some information and it updated THE WHOLE DATABASE WITH THAT ONE RECORD.. IF anyone could shead some light on what I'm doing wrong that would be great. Here is the syntax for my stored proc. CREATE PROC updateResults2 @id int, @address1 nvarchar (50), @address2 nvarchar (50), @CITY nvarchar (33), @ST nvarchar (10), @ZIP_OUT nvarchar (5), @ZIP4_OUT nvarchar (4), @home_phone nvarchar (22), @NEWPhone nvarchar (20) AS UPDATE Results SET address1 = @address1, address2 = @address2, CITY = @CITY, ST = @ST, ZIP_OUT = @ZIP_OUT, ZIP4_OUT = @ZIP4_OUT, home_phone = @home_phone, NEWPhone = @NEWPhone GO As said previously it ran but it updated the WHOLE DATABASE with the same change (WHICH I DIDNT WANT IT TO DO)!! Thanks in advance. RB
View Replies !
Modifying Inserted Or Updated Records Without Recursion?
I am looking for a way to update the information in the "inserted" logical record without having to call something like this: UPDATE tblX SET ValueY = ValueA/100 FROM tblX INNER JOIN inserted ON tblX.ID = inserted.ID because this may result in the update trigger firing (again). I'd like to avoid this. As a better example, if I have a table of transactions with cost and price info, plus a flag indicating whether the transaction has been merged to AR or GL, I want to be able to update cost or price directly, which will clear the flag and indicate that I have a batch out of balance. This I can do easily with Update(Cost) Or Update(Price). But, I also need to be able to change the supplier in the transaction record. If I do this, I want the trigger to fetch the new cost and price for me. If the cost or price change, I still want to update the flag. To get there I can call UPDATE tbl SET Cost = @EffectiveCost, Price = @EffectivePrice FROM tbl INNER JOIN inserted ON tbl.ID = inserted.ID which is recursive. I can't say "UPDATE inserted" because it's a logical table. Is there any way to avoid this and to set values in the middle of an insert/update?
View Replies !
Linked Servers And Cached Execution Path
I have a procedure that generates dynamic sql and then executes via the execute(strSQL) syntax. BOL states that if I use sp_executesql with hard-typed parameters passed in variables, the query optimizer will 'probably' match the sql statement with the cached execution path, thus avoiding recompilation and speeding up the results for heavily run procedures. Can anyone tell me if this is also true if the sql references an object on a linked sql server 2000 database? Technically, the sql is exactly the same, but I'm unsure if there is some exception due to the way linked objects are processed. Thanks!
View Replies !
Log All Updated Tables
We have a third party process that runs and updated several SQL tables. Is there any way to find out what tables are being updated and store it in another table?
View Replies !
Finding Updated Tables
I have a front end inteface that adds data to tables in a SQL database.My question is:Is there an easy way of finding out what tables are affected by the update? Is there a sp_ or DBCC command that would give me the update or altered tables?
View Replies !
How To Keep Tables Updated That Are Imported From Other Database
Dear All, I am making web application using Asp.net C#(Visual Studio2005). And Sql server 2000 as a back End. Actually I am fetching data from other database(named as retailexcel but in same server). I have wriiten a view (in retailexcel database) to get required data from four table of other database. My problem is when I fetch those data(about 40000 to 50000 record) It taking to much time. I tried catch feature of asp.net2.0. Time reduced but not significantly. So I am trying to import those tables to my database (named Inventory) from that retailexcel. Now problem is every day or two, record of those tables changes. How can I keep update my tables (which are importing from retailexcel) Please suggest me some idea. Thanks
View Replies !
Keep Remote SQL Data Tables Updated
Hello, I need some guidance in the best method to accomplish this task. I have a network with a SBS 2003 server and a SQL 2000 member server in the SBS domain. I have a remotely hosted website at discountasp.net with a SQL 2000 database. The website will host a modified e-commerce kit where corporate clients can order parts. I need to keep the products table on the remotely hosted site as updated as possible. The website orders are placed via email from the commerce kit and the fulfillment department proccesses the order against the SQL server in the SBS domain. Any ideas on how to keep the data updated on the website?
View Replies !
Insert/Updated SP From Multiple Tables
How do I insert unrelated statistical data from three tables into another table that already exist with data using an insert or update stored procedure? OR... How do I write an insert/Update stored procedure that has multiple select and a where something = something statements? This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this.... CREATE PROCEDURE AddDrawStats AS INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers) SELECT WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing), TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1), Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ), RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID ) FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID WHERE D.DrawingID = DS.CurrentDrawing GO
View Replies !
How To Find The Last Updated Tables In A Database
Hi, I am trying to create a data dictionary for a huge application which has aroung 300 tables in the database....when i perform any operation in the application some tables are updated.... can you help me to find out how can we find out the last updated tables in the database ??
View Replies !
Propagation Of Event Notification When Tables Are Updated.
I would like to propagate an event signal to an external applicationwhen a table in my MSSql2000 server is updated.Prog A; I have an external application adding records to a table.Prog B; I have another external application using this table as well.When Prog A creates a new record in the Table, how can I have Prog B benotified of the event without polling the table or creating a linkbetween Prog A and Prog B.Thanks.
View Replies !
Replacing Tables In SQL Express By An Updated Table Of The Same Name Through VB2008 Code
Hello, I have an application written in Visual Basic 2008, using a SQL Express 2008 database. This application runs in several locations on stand-alone PC's and there is no network connecting them. I have to maintain a SQL database in a central location an would like to be able to distribute updated tables from that central location by using an USB stick and replace the tables of the same name in the different locations with the updated ones on my USB stick. This I would like to do using code within my VB2008 application. Can someone please help me on the way to achieve this. Is it possible to give some concrete code examples, as I am a newbie as a SQL Server user. Many thanks and greetings, Michel
View Replies !
Insert Records From Foxpro Tables To SQL Server Tables
Hi, Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables: 1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables. 2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables. I only know the following ways to import Foxpro data into SQL Server: #1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables #2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables #3. DTS Foxpro records directly to SQL Server tables I'm thinking whether the following choices will be better than the current way: 1st choice: Change step 1 to use #2 instead of #1 2nd choice: Change step 1 to use #3 instead of #1 3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2 Thank you for any suggestion.
View Replies !
Linked Server ( Not Able To Access Any Tables Under LINKED SERVER From My DESKTOP Enterprise Manager
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied" if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER) And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP Thanks
View Replies !
Subject: BCM Install Error - Logfile && SQL Path Path && MSSQL.1?
When trying to install Business Contact Manager (BCM) for Outlook 2007, the setup failed and I was refered to a log file in my Local Settings/Temp folder. The log actually says that Business Contact Manager was installed sucessfully! BCM is supposed to install SQL Express 2005 as an instance or as instance if SQL Express is already installed. There is an MSSMLBIZ instance in Services.. Who can I send the Log File to for analysis and the fix feedback? When I first went into Computer Management and clicked on Services and Applications in the left panel, the error message appeared "Snap-in failed to intialize. Name: SQL Server Configuration Manager CLSID:{CA9F8727-31DF-41D2-975C-887D84903967} This message diappeared when I clicked on Services and Applications again. Under Services, there are 3 SQL services - one is an application that was uninstalled 3-4 weeks ago and I disabled this service. The other 2 are: SQL Server (MSSMLBIZ) and the other one is SQL Server (SQLEXPRESS) When I tried to start either of the last 2, the message appeared: Services "Could not start the SQL Server (MSSMLBIZ) service on Local Computer. Error 3: The system cannot find the path specified. Under Program Files/Microsoft SQL Server/MSSGL.1 folder is mostly empty. So, it seems like the Path in the Registry is not valid and that nothing is being installed in the MSSQL.1 folder. If so, how do I fix this? How do I get the BCM SQL instance to install and run properly? what do the messages in Services mean and how do I resolve these. Thank you! Gary
View Replies !
Updating Linked Records Across DB's
Here's a problem that I can't find anyone else has run into. I'm usingAccess and SQL Server, but the theory would be the same for any db.I have a large number of tables that contain linked records(intersection tables mostly). In the interest of space, I'llillustrate an example:tblStudents (ID, Name)tblTeachers (ID, Name)tblClasses (ID, Name)tblEnroll (StudentID,ClassID,TeacherID)I have about 10 people who each use a separatecopy of this database (in access). I want them (at the end of eachday) to be ableto update all the records that they entered that day into a databasethat I have setup on a server (SQL Server 2005). Both databases havethe exact same structure.Caveat 1: All of the classes, students, and teachers are not the sameon each database, but the server database should contain all of them.Caveat 2: There is no way for the clients to automatically insert intothe server, they are offsite and out of range.Herein lies the problem: when a record is inserted into the server froma client, all of the links are lost since the ID will be different onthe server that it was on the client.I don't think a simple update / insert query will work, and most db'sand languages don't play nice with recordset appends.What are your thoughts??
View Replies !
Save Updated Date When Row Is Updated
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
View Replies !
Problem When Applying A Snapshot When Tables Have Been Updated During Snapshot Generation
Hi I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated. Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name. The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors. I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name. All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.
View Replies !
SQL 2005: Unable To Edit Linked Table Records
Hi!I am able to add records to my SQL 2005 database using bound forms in myAccess application, but if I try to edit any records (not only the newrecords I create) using the same bound forms, I receive a write conflict.For the record, I am currently the only user of this application and my SQLServer database has only one userID. I used this userID to create all ofthe objects in the database and to connect to the database from Access. Thedata sources for the bound forms are linked tables, the record source typeis set to "Dynaset" and the record locks are set to "No Locks". "Opendatabases using record-level locking" is checked.The write error says that another user has changed the current record sinceit was opened (since I'm currently the only user this isn't possible) andasks me to copy my changes to the clipboard or drop the changes.Can anyone suggest what might be causing this? Thanks!
View Replies !
Linked Server To Oracle Via ODBC, Return Less Records
I have an Oracle 9i server. To access the data in Oracle, I setup an ODBC connection to it and am able to return data from it using WinSQL (a general ODBC database client). The SQL statement is simply "SELECT * FROM COLOR" and all 133 records returned properly. I need to copy the data from the Oracle server to SQL Express, therefore I set up linked server (by Microsoft ODBC provider) using the same ODBC connection as described above. The problem is: only 32 records returned. May I ask if there is any problem to this linked server setup? Thanks.
View Replies !
Return Records Through A Linked Server That Uses ODBC Data Source
Hi,I used sp_addlinkedserver to link to a remote server through ODBC.When I execute select count(*) from LinkSrv.SI.DBO.SIHeader in SQL QueryAnalyzer. It returns 13705 records. But when I execute select * fromLinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify somecolumns, select ODCOMP, ODPONO, ODVDCD from LinkSrv.SI.DBO.SIHeader.It returns more records, 1213 records.I guess there is something limit the return storage, but I can notfind it.Any suggestion will be appreciated. Thank you
View Replies !
Linked Tables
I have access tables which are linked to SQL database by ODBCconnection. By using Access, i can easily see my table contents. butwhen i am using ASP, i con not. I am getting this error messageMicrosoft JET Database Engine (0x80004005)ODBC--connection to 'SQL ServerMYDATABASE' failed.windows 2000, access XP, and sql 2000Please help me.Thank you
View Replies !
SQL Linked Tables
I have two SQL Server 2005 Databases running on the same server. I am working in a third database that needs to contain a union query to display data from one table in each of the other databases. In other words, within Database 3, I need to create View C.. This view will show takes Fields W & X from Table A in Database 1.. and union those values with Fields Y & Z from Table B in Database 2.. a) Does this make any sense? b) What is the best way to do this? (I searched, but anything with the phrase 'linked table' brought up results almost exclusively from the Access forums..) Thanks!
View Replies !
Linked Tables
Hi Our users are linking sql tables in Access. Our management wants to stop that. Is there any way to deny connection to sql server using access linking tables? Please help me. Thanks in advance Jaya
View Replies !
Linked Tables
I currently have a SQL Database with 1 Table. I have connected to the SQL table via MS Access 2003 via Linked Tables. I can import records from either an XLS or CSV file to the Link Tables all day. However, I cannot update nor delete records. I have given my account full DBO access to the SQL Database and Table but still cannot make any modification from within Access. Is this even possible for what I am attempting to do within Access? If so, please tell me what I need to modify within SQL. Thanks in advance!
View Replies !
Help With Deleteing From Two Linked Tables
Hi, know this sounds like a really silly question, but im having a bit of a problem deleting from two linked tables I have tried doing the following, however it says that it has an error near the ',' Any suggestions?? DELETE FROM meter_quote , client_details FROM meter_quote, client_details INNER JOIN client_details ON meter_quote.Client_ID = client_details.Client_ID Thanks, Hayley
View Replies !
Can You Have Linked SQL Server Tables?
Hello, I currently am in charge of creating a Sharepoint 2003/Exchange 2003 ticketing system. We have a client database (MS Access 2003) that has a linked table to our Exchange store, retrieving information we enter in Exchange regarding specific clients. I would like to move this database to SQL Server for performance reasons. Soon there will be many people accessing this database and I don't want the bottle neck to be Access. The only quirk I can think of is, can one link a table from SQL Server to Exchange. Or for that matter, can SQL Server have linked tables at all? One solution I can think of is to create a System Service that runs on the server, to periodically update the SQL table with the information in the Exchange store. But that could become costly over time with large amounts of network bandwidth being used for unnecessary updates. Is there maybe another solution I'm not seeing? Should I just stick with the Access database? Thanks Josh
View Replies !
Altering Linked Tables
I have a table (in Access) that is linked to the SQL server, and Ineed to add a column to it. I wrote the following:ALTER TABLE CensusADD COLUMN 'ActiveFacility' BIT;and I get a syntax error that I do not know how to solve. The columnneeds to contain yes/no data for each record.Any help for a struggling newbie?Thanks,Christine
View Replies !
Tables In Linked Server
HiI received the below error when trying to run an update from one SQLServer to another.I can insert and select. I cannot delete or update. The permissionshave been changed to allow the linked server user to carry outeverything, the linked servers are working but we cannot change thedata.We are stumpped and your help would be appreciated.Server: Msg 7306, Level 16, State 2, Line 1Could not open table '"charmfin"."charm"."TMP_BATCHPOSTING"' from OLEDB provider 'SQLOLEDB'. The provider could not support a row lookupposition. The provider indicates that conflicts occurred with otherproperties or requirements.[OLE/DB provider returned message: Multiple-step OLE DB operationgenerated errors. Check each OLE DB status value, if available. Nowork was done.]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowsetreturned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=TrueSTATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUTVALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyIDVALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocateVALUE=True STATUS=DBPROPSTATUS_CONFLICTING],[PROPID=DBPROP_IRowsetChange VA...Thanks in advance.Simon
View Replies !
Access/SQL Linked Tables
I have linked a number of tables from a SQL database to a Access front end. I am able to update all but one of the tables (the main table I need!!!). I get the error, cannot update record as it has been changed by another user although there is no other user. I believe it may be to do with the way the recordset is linked to the access database or the referential integrity of the table associations in the SQL database. I can change the data using query analyser without issue. Any help would be greatfully apreciated.
View Replies !
Do A Lot Of Linked Tables Cause Block?
Hello, everyone: There are a lot of Access and Excel tables linked to my SQL Server (SQL2K SP3 on W2K). The end users update those likned tables. I am wondering if there is the block problem. If yes, how to prevent that? Thanks. ZYT
View Replies !
#Deleted In Linked Tables
I have a problem that I hope someone here will be willing to assist with. We have a MS SQL Server 6.5 SP 4 box that we are linking to via MS Access (97 and 2000). The problem is when we are running a query on three joined tables, we are receieving #Deleted on only a few (under 200) rows. When we run this same query on the server using the Enterprose Manager we get all of the data. Also, when I import all of the table I can get all of the data. This only occurs when we run this query using the linked tables. IF someone knows the problem with this I would really appreciate sharing with me the answer. Thanks, Bruce
View Replies !
Linked Sql 7 Tables In Access
When you have linked sql 7 tables in access, first time you go into a linked table (or a form, report that uses a linked table), you get prompted for username and password (unless pw and username is the same in sql server and workgroup file). I have tried to make a workspace with password and username in a splash sceen. This worked in the good old days.... question comming up: I don't want the prompting for SQL server username/password, and using a workgroup file is not a good solution. Do you have a good solution?? sigurd PS! if the answer is No, don't answer. If the answer is Yes, please send the solution too.....
View Replies !
ODBC Linked Tables
In order to use Access 97 as your frontend and SQL 7 as your backend on a network (where the frontend is located on the same computer as the backend and people just map to Access frontend) where security for the data is recognized by your domain login account and what domain group you belong to..do you have to have the same System DSN on everyone's computer for linking? I am still having problems with people linking to the SQL server through the network and only my account is working correctly from my desktop. Everyone else who tries to link gets an ODBC failure error message when trying to open one of the linked tables. I have heard from one person that you must have the same system DSN on every client's computer but to me that makes no sense as I do not have the DSN listed on my individual computer...I am just going through the network as myself..not as an admin..and getting directly to the data. Everyone else can get to the shared folder on the server but cannot get to the linked tables. Any ideas?
View Replies !
Some Tables Do Not Appear When Using Linked Server
I just finished up setting up linked server from one of my database machines to another. After futzing to get permissions just right, it works great for many things: select top 5 * from altai.prep.dbo.simulation_status 0 Prepping Simulation is being prepped by the owner. 1 Prepped Simulation has been prepped by the owner. 2 Checking Simulation is being checked by the owner. 3 Checked Simulation has been checked. 4 Running Simulation is running. I can even see tables using sp_tables_ex: sp_tables_ex 'ALTAI', 'S%', 'dbo', 'prep', NULL, 1 TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- prep dbo Simulation TABLE NULL prep dbo Simulation_References TABLE NULL prep dbo Simulation_SimulationGroup TABLE NULL prep dbo Simulation_Status TABLE NULL prep dbo SimulationGroup TABLE NULL (5 row(s) affected) The trouble is, I have another table in this database called that begins with "S": Staging_Transition_States. If I try to access this table via the linked server, I get: select * from altai.prep.dbo.Staging_Transition_States Msg 7314, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI" for linked server "altai" does not contain the table ""prep"."dbo"."Staging_Transition_States"". The table either does not exist or the current user does not have permissions on that table. This table exists, sp_help for this table is below. Why doesn't this table show up in the output of sp_tables_ex? As far as I can tell, permissions for the tables that work and this table are identical. Thanks! --Andrew Name Owner Type Created_datetime -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ----------------------- Staging_Transition_States dbo user table 2007-07-27 11:29:47.790 Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- -------------------------------------------------------------------------------------------------------------------------------- pdb4 char no 4 yes no yes SQL_Latin1_General_CP1_CI_AS temp smallint no 2 5 0 yes (n/a) (n/a) NULL run smallint no 2 5 0 yes (n/a) (n/a) NULL sim_id int no 4 10 0 yes (n/a) (n/a) NULL time_step int no 4 10 0 yes (n/a) (n/a) NULL time decimal no 5 9 2 yes (n/a) (n/a) NULL status tinyint no 1 3 0 no (n/a) (n/a) NULL order int no 4 10 0 yes (n/a) (n/a) NULL comment varchar no 250 yes no yes SQL_Latin1_General_CP1_CI_AS created smalldatetime no 4 yes (n/a) (n/a) NULL created_by sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS updated smalldatetime no 4 yes (n/a) (n/a) NULL updated_by sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS Identity Seed Increment Not For Replication -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- ------------------- No identity column defined. NULL NULL NULL RowGuidCol -------------------------------------------------------------------------------------------------------------------------------- No rowguidcol column defined. Data_located_on_filegroup -------------------------------------------------------------------------------------------------------------------------------- PRIMARY The object 'Staging_Transition_States' does not have any indexes, or you do not have permissions. constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys -------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DEFAULT on column created DF__Staging_T__creat__047AA831 (n/a) (n/a) (n/a) (n/a) (getdate()) DEFAULT on column created_by DF__Staging_T__creat__056ECC6A (n/a) (n/a) (n/a) (n/a) (suser_sname()) DEFAULT on column status DF__Staging_T__statu__038683F8 (n/a) (n/a) (n/a) (n/a) ((0)) DEFAULT on column updated DF__Staging_T__updat__0662F0A3 (n/a) (n/a) (n/a) (n/a) (getdate()) DEFAULT on column updated_by DF__Staging_T__updat__075714DC (n/a) (n/a) (n/a) (n/a) (suser_sname()) No foreign keys reference table 'Staging_Transition_States', or you do not have permissions on referencing tables. No views with schema binding reference table 'Staging_Transition_States'.
View Replies !
Opendatabase And Linked Tables
Hi I am trying, to query, from the SQL Server query analyzer MyTable, which is a linked table residing in Access, SELECT * FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="S:MyDatabase.mdb"; User ID=Admin;Password=' )...[MyTable] Im not being allowed to do so: the error is the following: -------------- Could not open table 'SHRTCKN' from OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. The specified table does not exist. OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IOpenRowset::OpenRowset returned 0x80040e37: The specified table does not exist.]. ------------------------ Does anybody have any comment? thank you
View Replies !
A Query Where Two Tables Are Linked To The Same Another Table
Hello,I'm not an expert in SQL, if you could help me for that littleproblem:I had tree simple tables with their fields:[Client] IdClient, Param[Sale] IdSale, IdClient, Param[Param] IdParam, ValueHow can I retrieve a recordset with this columns ?IdClient, IdSale, ValueOfParamClient, ValueOfParamSaleThe problem is that I can retrieve a Param for one table (Client orSale) like this request :SELECT Client.IdClient, Sale.IdSale, Param.ValueFROM(ClientINNER JOIN SaleON Sale.IdClient = Client.IdClient)LEFT JOIN ParamON Param.IdParam = Sale.ParamBut how can I retrieve the Param of the another table in a simplequery ? (because I would also like that it works for access)Thank for your help,Marc
View Replies !
|
TRACKER
