I've created a linked table to Access. I want to be able to delete records and modify existing records through this linked table. I used the account with sa privelleges yet i am unable to delete/update any records.
Does anyone know if there is any fix or am i doing anything wrong ?
Hi Everyone,
I have set up a link from ACCESS to a SQL 7.0 database using ODBC (File DSN saved on a shared DRIVE). The link works well only from the workstation where the link was created. But How can I create a link so a group of users can view the linked table in ACCESS without type a password? Any suggestion is appreciated.
Lunjun
Strange one here - I am posting this in both SQL Server and Access forums
Access is telling me it can't append any of the records due to a key violation.
The query:
INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;
Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.
There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.
I can manually append the records using cut and paste with no problems.
I have tried re-linking the tables.
Any ideas?
Thanks,
Brad
I'm using ODBC to link a SQL table to access and using Access as the frontend, so to speak. Trying to write a query, but I get Timeout Error.
I need to resolve the timeout error, I've tried several things in the ODBC
configuration, but no solution yet.
thx
jm
I currently have a database that I can update in Microsoft Access by going into datasheet view of the table and just changing the value of a given field.
Here is the problem. I have made a copy of this database by backing it up and restoring to a new database. Now when I go into Access datasheet view to update, I get the following error: "ODBC--update on a linked table '<tablename>' failed". I have tried restoring to a database on the same server as well as others and have checked for differences set for each database as well as differneces in indexes. Any suggestions would be appreciated.
Thanks in advance
Keith
Can the DTS execute SQL task recognize a table in an Access database that is linked from another Access database?
I'm attempting to use a DTS Execute SQL task connected to Access1.mdb to make a new table in Access1.mdb that is using a Subquery from a linked table in Access2.mdb . (I know it sounds convoluded..) Its giving me an error in my FROM clause as if it can't recognize the linked table from Access2.mdb.
The reason why I'm doing this is that I'm not sure how to run an Access make table query from DTS that will overwrite the existing table in Access. I get an error that the table already exists.
Any Help?
I'm trying to set up a linked table from SQL Server 2005 to MS Access. The .mdb file resides on a network share and I'm unable to connect to it. If i copy the database to a folder on the server that SQL is installed, it works fine. Any help would be greatly appreciated. Below is the error message that I'm receiving.
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LINKSERVER".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LINKSERVER" returned message "The Microsoft Jet database engine cannot open the file '\serversharedatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.". (Microsoft SQL Server, Error: 7303)
I have access97 front end , with linked tables to sql server 2005. when i change a table ID column (type int) to primary key so it is updatable, then try open the table i get odbc error with no msg, if i alter table again to remove primary key option table opens fine but is not updateable. any ideas would be a great help
View 5 Replies View RelatedGood afternoon one and all,
I have the folowing problem that I could use some help with :
I have an SQL server database acting as a back end to an access dbase. The SQL srv table contains over 32 million records and I am trying to use an append query (in access) to import a further 2 million records to the SQLSRV table. The append query fails with the message 'Insert on table bcdsales failed' followed by an ODBC timeout error message. I can append one record fine but a mass import fails.
Unfortunately i can't use SQL srv to do the import (internal policy says we must stick with access front end for now).
Any and all ideas welcomed.
TIA for your time and attention
Gurmi
Hi,
I have an Access 2003 front end that contains a number of linked tables on SQL Server 2005 SE. I recreated the application on a second network for testing and was able to use the Linked Table Manager to refresh the database connections. The problem is when I try and add another linked table. I select Link Tables from the menu and then when I select 'ODBC Databases()' from the 'Files of Type' list box, the Link window closes right away.
Any suggestions?
I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.
This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.
Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.
I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.
Has anyone run across this before?
Thanks!
-Ben
I linked an SQL server table into Access, but when I open the table in Access, no data shows. Do I need to set some permissions on the sql table?
View 1 Replies View RelatedI have used an access linked table to import data from a sharePoint list hosted on SharePoint 2007 server.
I hope someone else has done this. (1)I Does anyone know of any other way to pull data from a SharePoint 2007 list.
(2)Has anyone pushed data to a SharePoint List from a SQL Server table?
I tried doing that via the linked table but the destination task of the data flow task does not allow me to push data into the table.
(3) Has anyone designed an SSIS package to write data to an AccESS table from a SQL Server table?
Any help would be appreciated
OS: Windows XP (SP2)
App: MS Access 2003 (SP2)
DB: MS SQL 2000 (SP4, 2040)
User is using MS Access linked table to query database (using ODBC). User can open the query in MS Access. However when the user goes to export the data to MS Excel format, the user receives the error message:
"Unexpected Error from External Database Driver (22)."
A trace on the db, reveals that the user process is attempting to log in to the server as 'Admin'. However, when the query with the linked table is first opened, the user is prompted for username/password (non Windows authentication).
Any ideas? I googled and found some stuff, but nothing directly related and most of what I found was supposedly fixed with SP1.
Any help appreciated. I am not an Access guru.
Regards,
hmscott
We migrated a MS Access 2003 mdb into MS Access 2007. The mdb has linked tables to SQL Server via a DSN and utilizes a mdw file. In 2003, the username/password is "passed" to SQL Server, so the UID/PWD that is used for opening the mdb, is used in SQL Server.
Opening the same file in 2007 using the same mdw, gives a secondary login on SQL Server.
Is there a way to have MS Access 2007 pass the UID/PWD to SQL Server on linked tables, the same way that 2003 does?
Thanks!
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
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.
Gurus,
While creating Linked server from SQl server to Ms access I am getting the following error
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ACC".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ACC" returned message
"The Microsoft Jet database engine cannot open the file '\xxxxxxxxxxxxxxxxxxREM_Prod.mdb'.
It is already opened exclusively by another user, or you need permission to view its data.".
(Microsoft SQL Server, Error: 7303)
But rem_prod.mdb is closed. I just created the Access database. There is no password and the shared directory is having full of read and write access. Can anyone suggest me where was the problem. or am i missing something..?
Thanks
Krishna
I have SQL Server 7.0 running on a NT server. I need to get data from an access database on another NT server on the network. Can I have it mapped to a logical drive and create a linked server and get data to be used in my stored procs. Please help !!
Thanks
Hi,
I am running SQL Server 7.0 and am trying to set up a linked server to an access database so I can use a SQL7 stored procedure to update the access database.
I create the linked server as:
sp_addlinkedserver @server = N'LinkedServer',
@srvproduct = N' ',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@datasrc = N'MyServerest2.mdb',
@catalog = N''
GO
I then try setting up the security in the security tabs to impersonate a user I have set up in the access database.
I also have NT security on the directory of the access database to limit the users who can access the directory.
My problem is that when I try to run the sql:
"select * from LinkedServer...IMS" (IMS is the tablename) I get the following error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file 'ASPECT04accpactestest2.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
If I add "Everyone" tpo the security level on the NT directory it works fine, so I obviously have a security problem.
Can anyone tell me what user the sql is running under.
any ideas of the best way to set this up.
many thanks for the help
dave
Are there any documented bug/incompatiblities with Access 2000 linked to SQL 7?
I have an Access form that is used to do data entry to SQL7. In Access 97 the forms worked great, but now in Access 2000, if a user pastes plain text into a field, when they save the record all the text is converted to questions marks.
TIA
John Denver
jdenver@epix.net
I am new to SQL server. I have a pbm setting up a linked server to MS Access using ODBC. My MS Access database is in the Netware server. I have mapped the NT server to this Netware server using a binary account. Then I have setup the ODBC connection. I tested the ODBC its ok. But when I try to view to tables in the Linked server it gives me an error "Disk or Network Error". I am breaking my head for the past few days. If you have any solution. Pls reply. Thanks in advance.
View 2 Replies View RelatedHi
we have some Access databases that are password protected. I get an error when I try an access these through a Linked Server. If I remove the password there is no problem.
I have tried adding the password to the security Information with a user of admin but this fails with a 7303 error. Should I not use admin as the user ??
Any ideas ???
thanks
Gary
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.
After converting to SQL Server 2005 and VS2005 I am having a problem connecting to Linked Servers for Access databases. I have searched and read everything I can find, but the solution is still not evident. I have a lot of data integration and data conversion procedures to run and I am stuck at this point not understanding exactly what the problem is and how to resolve it. I believe it is somehow related to "double hop" and permissions with .NET. Many of the discussions are related to using .ASP but that is not involved in this situation.
The Linked Server is defined using the UNC path. The machines are not in a domain. They are in the same LAN segment. Connections will be made without using a security context is specified. A sample Linked Server definition:
EXEC master.dbo.sp_addlinkedserver @server = N'ACCESS EDUCATION', @srvproduct=N'OLE DB Provider for Jet', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'\Tacir2k3InfrastructureDatabasesJuly 2005 DatabasesEducationEduDb2005_TR_Db.mdb'
The error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ACCESS EDUCATION" returned message "The Microsoft Jet database engine cannot open the file '\Tacir2k3InfrastructureDatabasesJuly 2005 DatabasesEducationEduDb2005_TR_Db.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Machine 1 (Developer Workstation): XP SP2 with latest security patches; SS Management Studio, VS2005
Machine 2 (SQL Server server): Windows 2003 Server with SS2005. The Linked Servers are defined on an SS2005 instance on this machine.
Machine 3 (File server): Windows 2003 Server that contains the Access DB files
When I run SS Management Studio on Machine 2 (the database server), I have no problem accessing the Linked Server pointing to an Access DB file on Machine 3 and can compile or run SPs referencing the Linked Server.
When I run SS Management Studio on Machine 1 to execute an SP that references the Linked Servers, I get the above error.
When I use Machine 1 to compile an SP that references the Linked Server, I get the above error.
I tried granting Everyone Full Control to the folder that contains all the Access database subfolders but I get the same error when running from Machine 1.
I am trying to configure a linked server to a local Access database with no security and only one table.
I set it up and tested on a test machine first and all works fine. I then did the same on our production box and receive the error below.
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Not a valid account name or password.]
I then set it up on another production box and it worked for a while but now I get a different error.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Cannot open database ''.
It may not be a database that your application recognizes, or the file may be corrupt.]
Is there a server level setting that could be different on these machines that could be causing this?
Hi,
I have an Access database (name "Shipping") linked to SQL Server. The Access database is not secured. My access file is located on the same machine as the instance of SQL Server, in a shared folder that can be accessed by any user. The file permissions are set to full control for Everyone. I linked Access with the following procedure:
EXEC sp_addlinkedserver
@server = 'Shipping',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:ShippingShipping BackEnd.mdb'
then modified the linked server login mapping with:
EXEC sp_addlinkedsrvlogin 'Shipping','false',NULL,'Admin',NULL
Now, since I have domain administrator permissions, I can run a stored procedure in SQL Server that does a Select in the Access database without any problem. But when a normal user tries to run the procedure, he gets an error #7399. If I give this user domain administrator rights, he's able to run the procedure. There is something I don't get, with normal user permission, this user can open the Access database through the shared folder...
Thanks
-Steve
Hi,
I'm working with MSSQL2K+SP3a, Standard Edition. I defined linked server (MSDE).
On the local server, I can do
select * from [testDB].[INFORMATION_SCHEMA].[TABLES] -- local
select * from [testSRV].[testDB_far].[dbo].[sysobjects] -- linked
but not
select * from [testSRV].[testDB_far].[INFORMATION_SCHEMA].[TABLES]
How can I access the INFORMATION_SCHEMA on the linked server ?
Thanks,
Helena
I'm trying to create a linked server to an Access database that resides on a separate machine.
On my PC, I can create a link to the Access db, and view, update, add and delete data. If I create the same linked server on our production Server, I can again view, update, add and delete data. All's well so far.
If I now go back to my own PC (used for developing) I cannot access the linked server on the production machine.
The Access database is stored on a separate PC, so the I'm linking to a remote db. As I said this works fine if I'm sat in front of the PC that the linked server is created on - but not if I use a client PC to connect.
I create the linked server using the following command:
exec sp_addlinkedserver
@server = 'AccLinkedServer',
@provider = 'Microsoft.jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = '\DatabaseServerAccessDatabase.mdb'
If I run the SQL statement:
SELECT * FROM AccLinkedServer...AnyTableYouLike
I get this error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\DatabaseServerAccessDatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
I've searched in the MS knowledge base, and this forum and followed all of the advice that was available, but it still won't work.
Anyone got any other advice, before I go totally insane with this.
Cheers.
I am trying to create a Linked server between Access 97 and SQL2K5
provider=Microsoft.Jet.OLEDB.4.0
MDAC version installed on SQL server system is 2.8
with same configuration if i convert MDB file to Office 2000 then its working fine
Please advice
There seems to be a lot of confusion around the groups about linkingto an Access mdb with the SQL Server Jet OLE DB provider and I haventbeen able to find a straight forward solution. Basically, I have anAccess MDB (A2K) on one server and a SQL Server DB (2005 std ed.) onanother - Both on the same network. I'm trying to create a linkedserver object in the SQL server to view data in the mdb. I've set itup and it works - but only from the server machine itself. If you tryto connect the the linked server from any other computer on thenetwork you get the usual access denied / file in use error.The fact that I can use the linked server from the server box itselfbut not from another pc on the network makes me think that it may be apermissions problem. But I am logging in with full Admin rights andstill no joy. Also there is no workgroup security on the mdb, so thatsnot the problem. I've used the surface editor to remove anyrestrictions that may cause problems, OLE DB connect, OPENROWSET etc.but still no joy.I've tried mapping the mdb's location on the server so I could use astandard filepath, rather than a //Server-Name/... path. Again, worksfrom the server, but not from any client PCs, so no joy there either.In frustration, I copied the mdb to the same server and viola - fullaccess to the linked server from anywhere. But this is no good, I needthe mdb file to stay where it is. An mdb full of linked tables wontwork... they don't show up in the linked server.So that's it - out of ideas! Am I just going to have to accept thatlinked server objects are limited just to mdb files on the same servermachine, or is there something I'm missing??? Firewalls, servicelogins, server settings.... something one of you gurus out there knowabout that might be the key to making my headache go away!!!Any input gratefully recieved!!!
View 1 Replies View Relatedwindows xp pro
iis 5.0
office 2002
sql server express 2005
linked server: lkdb
Provider: Microsoft.Jet.OLEDB.4.0
product: Jet 4.0
data source: d:db est.mdb
provider string: Provider='Microsoft.Jet.OLEDB.4.0'; Jet OLEDB:System database='d:dbSecured.MDW';Data Source='d:db est.mdb';user id=rep; password=1234;;
got error message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "lkdb" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "lkdb".
I've set up a linked server to an Access database and read from the linked database in a stored procedure. No problems when I execute the stored procedure from management studio and the Access database resides on a local drive.
However, when I move the database to a network share drive and run the proc through a scheduled job using SQL Server Agent I get the following error --
"Executed as user: C-SGROUPactprod. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274)."
I've spent days trying to resolve this problem and tried many different setting to fix the problem, but no luck yet.
Under the Security setting for the linked server I have "Be made using this security context" selected with a valid id/password. I am able to login to the server where the database resides using the id and password and successfully open the database.
Don't know what to try next. Any suggestions?
I'm using SQL Server 2005 Developers edition.
Thanks.