Permissions Issue When Linking To MS SQL 2000 DB From MS Access XP
Jul 20, 2005
Hello,
We have a database in MS SQL 2000 and I'm trying to link to it from MS
Access XP. I've created a user with full access to one specific table
in the MS SQL database, but Access won't write to it.
I can open this table in MS Query Analyzer and log-in as the user, and
I can do update, delete, and select queries just fine... but when I
link to the table from MS Access using the same MS SQL
username/password, I can only read the data... no write or delete.
Since I can update data fine in MS Query Analyzer, I assume
permissions are setup properly. Anyone know why I can not edit the
linked data from MS Access?
Thanks,
Alex.
View 1 Replies
ADVERTISEMENT
Oct 23, 2004
Hi, I need to link an Access 97 database to sql server 2000. I can't use Enterprise Manager so I have created a simple asp.net page using sp_addlinkedserver and sp_addlinkedsrvlogin.
The mdb file is on a network share, then I have permissions problems: here is a sample
sp_addlinkedserver 'test', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'\SVKNFS080Pshareaccess97db1.mdb', 'efco'
sp_addlinkedsrvlogin 'test', false, 'sa', 'Admin', NULL
I have no problems executing these stored procedures (the linked server is added in sysservers table), but when I try to query the access database
SELECT * FROM test...profile
I get this error
Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message:
The Microsoft Jet database engine cannot open the file '\SVKNFS080Pshareaccess97dboutlet.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
The mdb file is not locked, I think is a permission's problem.
Can anyone help me ?
Thanks in advance
View 1 Replies
View Related
Jul 11, 2007
Hi,
I have an MS-Access database on a shared drive.The Access database has tables linked to a SQL server database.When I try to query the tables in MS-Access database by accessing the share drive,I get the ODBC--call failed error.So I tried creating an ODBC driver for the SQL Server and when I try to link the tables,no tables are displayed.Can anyone help me out with this problem?
-Ruth
View 3 Replies
View Related
May 4, 2007
I am have created a test table in enterprise manager v8 and linked this table to an access database. I am able to append to the table from access but cant delete or update.
Ive set the permission settings to allow the user to update and del so cant work out why this isnt working?
ANyone got any ideas?
View 2 Replies
View Related
May 1, 2008
Hello,
Could someone please tell me (step by step as I am new!) how to establish a link from reporting services to an access database?
Thnaks,
RC
View 1 Replies
View Related
Mar 22, 2001
I created a SQl Server 7 database and used an Access 97 frontend on the same computer. The Access frontend uses linked tables to reach the SQl server. The security setup I have is based on the NT Domain Accounts and whatever group
I have them in (example: Mr. D is in Marketing therefore he is in a NT Domain
Group called "SQL_MARKETING"...the SQL Server recognizes that everyone in this
group has access to whatever role I have designated for it in the database").
I can access the linked tables fine on my computer at my desk or at the server. Another person with the exact same administration rights as me can
get to the server through the network neigborhood or by mapping..and they
can access the Access frontend with no problem from their computer. But
when they try to open a linked table they recieve an error message that
the ODBC connection has failed. We are all running Access 97 service pack
2 and another person and me have the exact same administration..but only
I am able to access the Access linked SQL tables but not him.I tried deleting
the ODBC system dsn on the server and the linked tables and then re-adding them and the ODBC link but to no avail. Does anyone know why this would
be occuring? Thank You in advance.
and the
View 2 Replies
View Related
Jul 10, 2014
I tried this syntax in SQL Mngt Studio which i got from SQL menu.
EXEC sp_addlinkedserver
@server = 'Test2',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:MSOfficeonus.mdb'
GO
Linked is created but when i tried to open the table from bonus.mdb using the linked servers-Test2-Catalogs-System Catalogs-default-Tables the tables are not showing and throwing an error of OLE DB provider ....... for linked server "Test2" reported an error. Authentication Failed. Cannot initialize..
View 3 Replies
View Related
Feb 6, 2006
We recently upgraded a number of databases from MS Access 2000 to SQL Server 2000. I need to make it so that users can display the data from the SQL Server so I created some link tables in Access for them to view the data without using SQL Server Enterprise Manager. This works fine in tables with only a few thousand rows of data. However some of the tables have 100K+ rows of data, and these are not displaying the data. When someone tries to view the data in one of these tables all they see is #Deleted in every column. Is it possible to link to tables in SQL Server with large numbers of rows and view this info in Access?
Miranda
View 5 Replies
View Related
Dec 7, 2007
OK here is my situation: I work in a laboratory with a 10 year old instrument that stores results in an Access 97 DB. The DB is beautifully designed and implemented. The problem is that since the software is so old, once the DB gets above a few MB in size the application starts to fail. So their solution is to Backup (make a copy) and Cleanup (delete data older than X days) once a week. So what I end up with is a bunch of copies of the DB, which fragments the data that I need. In order to get the data results I have been using an Access 2003 DB linked to this one (because I can't create Views in that database). The lab users then use the linked DB directly or I have written some automation code in Excel VBA (ADO) to simplify it for the other users. This allows us to query data immediately as it is produced.
My hope is that I can somehow get all of the data into a SQL Server DB. Now I have imported one of the backup copies of the DB in and the table structure and data imports fine into SQL Server, but I end up with duplicate rows because the different DB backup files have overlapping data, and for some reason there aren't PKeys. Also, I'd prefer to have some way of automating the input. Something like a trigger on the Access table so that any new data is imported into the SQL DB automatically. I doubt this is possible, but that is generally what I am trying to do and I am out of ideas. The ultimate goal is that the SQL DB will always be "updated" and I can use that as my query source and take advantage of the power of SQL Server.
Hopefully that all made sense... if anyone has any ideas I would love to hear them.
Thanks for your time.
View 4 Replies
View Related
Apr 21, 2015
Is there a way to link MS Access tables with SQL Server tables so the client app sees them as tables from one database?
I have tried linked server but it appears that it gets stale after a while and needs to be re linked from time to time which is not very practical.
View 2 Replies
View Related
Jan 22, 2007
Hi,
I have an existing Access DB with many Tables, Forms, Queries and Reports. I need to put a SQL Server Back end on this database.
Do I need to code a mirror images of these objects or is there some way to export these into SQL Server?
Also, where do I deal with assigning usernames and passwords? Access or SQL Server. I need to have different views for different users based upon their rights (user, admin)
Thanks,
Mark
View 4 Replies
View Related
Oct 4, 2006
Hello,
I am using MS Access 2000 as a front end to a SQL Server 2000 db. When I link to the SQL Server db using a DSN, the SQL Server table pk's and indexes are not coming over with the link. The pk's and indexes do exist in SQL Server. I haven't had to link to these tables in over a year, but they used to link just fine.
Do you have a suggestion as to where to look at this problem?
Thanks you - Darryle
View 7 Replies
View Related
Jun 6, 2000
We recently installed SP2 on our SQL Server 7.0 database, and it appears to have changed the way Access databases are linked. Any user other than SA cannot connect to the linked Access tables. We receive the error 7302, which is 'Could not create an instance of OLE DB provider...'. Before the SP2 upgrade, this link was working fine, and non SA personnel (DBOs included) could connect to the linked DB and run queries.
We tried dropping and recreating the Access link, and it still has the same results. We also tried connecting to another Access database, and had the same results. The Access databases we are trying to connect to have no built in security, and the sharing permissions appear to be working because the SA can connect.
Has anyone else run across this error? Is it a problem with SP2?
View 1 Replies
View Related
Mar 8, 2004
Hi,
I am using an MDB file to talk to a SQL Server. I would like to use pass-through queries to keep as much of the processing on the server as possible. However, I have been told that the results of such queries will be read-only. How do I keep the processing on the server but generate read-write answers to my queries?
Thanks
Matt
View 3 Replies
View Related
May 9, 2008
hello
we suppose to link the form in MS Access with Oracle
can anyone tell me how to write the code for these buttons
i tried to write as I learned in VB.NET,
But it seems doesn't work :(
http://www3.speedyshare.com/data/191404757/10803567/78224332/db2.jpg
View 2 Replies
View Related
Aug 30, 2006
I'm having problems seeing the correct data type when linking my Access tables to MS SQL Server 2005. My varchar(max) fields are showing as text(255). I'm using the SQL Native Client.Has anyone else ssen this issue?
View 3 Replies
View Related
Jul 20, 2007
Hi,
I have a default instance that is Server1 (2000) and a named instance Server12005 (2005)
How do I link theses servers together so that my 2005 database can query the 2000 database.
Cheers
Leon
View 1 Replies
View Related
May 22, 2008
I am trying to add a linked table to my server, it is an access table.
Here is the code i am using but i get an error:
EXEC sp_addlinkedserver
@server = 'AITdb_be2',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'Access',
@datasrc = '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb'
GO
-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'AITdb_be2',
@useself = 'false',
@rmtuser = 'Admin',
@rmtpassword = ''
GO
-- List the tables on the linked server
EXEC sp_tables_ex 'AITdb_be2'
GO
ERROR:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2" returned message "The Microsoft Jet database engine cannot open the file '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2".
View 3 Replies
View Related
Dec 27, 2007
I have a sql server 2005 db.
Is there anyway I can link access to be used as a frontend by my client for viewing the data?
is this possible through linked table manager?
Please advise?
View 1 Replies
View Related
Dec 3, 2007
Hi there,
I just upsized my access database which has several tables and query linked to one of the tables residing on a seperate access database. When I do add in the diagram section, I see only table, then my question how would I be able to do the same thing under SQL Server 2005? thank you
View 3 Replies
View Related
Nov 17, 2006
After Upsizing a table to sql I linked to that table using access db
Now when i use one of my forms i get a [Invalid Object name "tablename''], not sure why but i am clearly link and the table is in sql!
Can you help!
View 1 Replies
View Related
Jan 6, 2008
Re: Possible Vista-only security issue opening a TCP connection to SQL2005
Please help. I have a problem that appears to be rare. I'm testing the culmination of a year's work and the error I'm getting is at the most basic level (the SQL connection) while everything else so far is working as coded.
My client is Windows Vista, apparently no firewall (I say apparently as in recent years MS have put more and more facades over the actuality of the operating systems), my server is Windows 2003 Server b3790 SP2 with SQL 2005 Standard 1399.06, again no firewall.
PortQuery 2.0 reckons that the server is listening on port 1433, too, so it doesn't seem like a firewall issue.
Nothing reported in either Event Viewers.
Here's the stack dump:
Unhandled Exception: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to
SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider:
TCP Provider, error: 0 - An attempt was made to access a socket in a way forbidden by its access permissions.)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Robot.Data.Support.ExecuteStoredProcReader(String connectionString, String procName, IDictionary`2 parameters) in P:Visual Studio 2005ProjectsRobotRobot.DataSupport.cs:line 44
at Robot.Data.Analytics.FetchableCollection.ExecuteByIYLT(String criterion) in P:Visual Studio 2005ProjectsRobotRobot.Data.AnalyticsFetchableCollection.cs:line 78
at Robot.Data.Analytics.FetchableCollection.Robot.Core.Interfaces.IFetchable.Fetch(String criterion) in P:Visual Studio 2005ProjectsRobotRobot.Data.AnalyticsFetchableCollection.cs:line 49
at Robot.Data.Analytics.FetchableCollection.Fetch(String criterion) in P:Visual Studio 2005ProjectsRobotRobot.Data.AnalyticsFetchableCollection.cs:line 41
at Robot.Data.Analytics.AnalyticsDalc.Prefetch(String criterion, AnalysisTypeEnum analysisType) in P:Visual Studio 2005ProjectsRobotRobot.Data.AnalyticsAnalyticsDalc.cs:line 167
at Robot.Data.Analytics.AnalyticsDalc.FillRankedCollection(RankedCollection& emptyRc) in P:Visual Studio 2005ProjectsRobotRobot.Data.AnalyticsAnalyticsDalc.cs:line 115
at TestHarness.Program.Main(String[] args) in P:Visual Studio 2005ProjectsRobotTestHarnessProgram.cs:line 35
And here's the connection string, which is built using the SqlConnectionStringBuilder:
"Data Source=192.168.2.2;
Initial Catalog=Research;
Integrated Security=False;
Persist Security Info=False;
User ID=usr_research_dalc_www02;
Password=usr_research_dalc_www02;
Pooling=True;
MultipleActiveResultSets=True;
Network Library=dbmssocn;
Application Name="Analytics Data Access Layer""
I have tried an old project which used to connect to the same server using a more basic connection string (it was a project which populated my Research db with fake data) and it also fails. When I ran this project successfully I'm pretty sure it was on an XP client.
I have also ran my TestHarness .exe on another client (WinXP) and it appears to pass this point and raises an exception that I believe is caused at a later point - so it looks like a Vista client issue.
Thanks in advance for your help,
Luke
P.S. Some losely related thoughts; the MSDN forum system still logs me out on Vista when I try to submit a thread, please sort this out! You can't expect your customers to run around deleting cookies. Also, when will you finally deprecate the default Named Pipes!? And finally, having that "does not allow remote connections" error spewed out with all connection errors is a really bad design choice, in my opinion.
View 4 Replies
View Related
May 10, 2006
Looks like the Office 2003 MS Access has no support for creating a linked server and implement design changes. While you could link to the SQL 2005 tables, which show up as queries in MS Access 2003, any further use of the linked queries are not supported unless the Access 2003 version came out after the SQL 2005 release. What kind of an update is needed, or fix is needed so that Access projects can be developed?
View 6 Replies
View Related
Aug 18, 2015
I have trying to execute the Sendmail task in my development envinorment i face this error..I have given the clear details error message below,Please have a look.
[Send Mail Task] Error: An error occurred with the following error message: "Failure sending mail.  System.Net.WebException: Unable to connect to the remote server  System.Net.Sockets.SocketException: An attempt was made to access a socket in a way forbidden by its access permissions ".Alternatively  i use the  SMTP connection to create the subscription as well,its working properly.
Here we are accessing SMTP connection manager as Virtually.Here in my client network we are using Macafee Anti virus ,We have excluded the Rsconfigration file in the excluded list.I dont know why this problem occures again using Sendmailtask in ssis?
View 2 Replies
View Related
Aug 8, 2007
I am trying to link an existing SQL 2000 server to a new 2005 box, but for some reason it will not link. I tried another 2000 server and that one worked perfectly well.
When I link it and perform a simple query...
select a, b from [server1].[db1].[dbo].tablec
I receive a 7314 message with text "OLE DB provider "SQLNCLI" for linked server "server1" does not contain the table "].[db1].[dbo].tablec". The table either does not exist or the current user does not have permissions on that table.
What's odd is that I was able to link the problematic server to other SQL 2000 servers with no previous issues.
Any ideas?
Thanks,
Steve
View 3 Replies
View Related
Jul 23, 2005
I need to connect SQL Server 2000 to DB2 on z/OS through DB2 Connect 8.I can successfully connect and query data through a System DSN, buttrying to link the server using this DSN and MSDASQL fails (can't seemto find the DB2 Connect server). I've also tried IBM's OLE/DB driver(IBMDADB2).Has anyone successfully linked to DB2 on a mainframe through DB2Connect 8.x via either ODBC or OLE/DB drivers?
View 3 Replies
View Related
Nov 14, 2007
I have SQL Server 2005 on IA based server and SQL Server 2000 and I have to make linked server on SQL 2005. But I had an error: The OLE DB provider "SQLNCLI" for linked server "DENEB" reported an error. Authentication failed.Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "DENEB".OLE DB provider "SQLNCLI" for linked server "DENEB" returned message "Invalid authorization specification". (.Net SqlClient Data Provider).
I searched Web for resolve this problem and find what i need run sql script instcat.sql for the Sql Server 2000.
Script was saccesuful complete, but problem did not resolve.
Please help me!
View 1 Replies
View Related
Aug 21, 2006
Hi,
I have an app that creates and attaches tables from SQL Server into an Access mdb using an ODBC link from VB6 using DAO 3.51 on Windows 2000. Recently I upgraded to SQL Server Express edition and tested it. It worked fine against the Express Edition. So, I installed SQL Server 2005 Standard Edition on our Server hoping that they operate identically. How wrong one can be! the same code that worked against Express Edition does not work on Standard Edition.
It is strange that the App works for about 5 seconds which attaches about 38 tables and after that the connection drops and I have a hell of a time to get the rest of the tables linked. The error that comes up is:
Connection failed:
SQLState: '01000'
SQL Server Error: 10060
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 11
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.
The PC that I am using to connect is in a different domain to the Server where the SQL Server database resides. I thought that domains might be the reason. But even joining the PC to the same domain as the server still does not work and behaves the same. So I do not think it is a domain related issue.
I have also looked at the SQL Server and database configurtaions. I cannot see anywhere any specific time out of around 5 seconds. Even the time out through the SQL Server ODBC driver is set to 30 seconds by default as far as I know. So, it looks like there is a time out somewhere but I am absolutely confused as to where this 5 second time out is comming from.
I also thought permissions on the database or tables might be the issue but after looking at the database and table permissions, they all look OK. The user has prettymuch the same access as the SA on the database.
Has anybody came across this problem? If so, I appreciate if someboday can give me a hint as to where I can start.
Thanks,
View 3 Replies
View Related
Oct 26, 2006
I need some help I have a 2003 MS Small business server with MS SQL 2005. I have a access front end database and SQL server back end. I need to connect a web site form in iis on the same server to the sql tables . I am generating errors in the event log when I submit the form data from the web, €œLogin failed for user SERVERNAMEIUSR_SERVERNAME! (CLIENT:192.168.1.37) !
Can anyone direct me to a paper that will step me through setting up this access?
View 11 Replies
View Related
Jun 5, 2006
Hello,
I was wondering whether it is possible to query linked servers in SQL Server Management Studio Express 2005 if those servers linked to are SQL Server 2000 machines?
I have added some linked servers to express and queried them, however the login timeout is expiring. I thought it might be because of an incompatability.
Cheers,
Damien.
View 1 Replies
View Related
Dec 8, 2006
I know this is an easy one for most of you but this is new territory for me.
I am trying to link a production application database to an existing SMS database.
Both of these databases are SQL 2000. I have sa access to both of them.
I have an accreditation application that deals with internal processes within our active directory network. We are using SMS database collections to provide the discovery for reporting of this application database and I want to compare the collection to the existing data between the 2 databases.
For simplicity I will define them both.
Application Database we will call €œA-Database€?
SMS Database we will call €œB-Database€?
I have gone into the A-Database and gone to linked Servers under the security node in enterprise manager. I created a new linked database and selected the SQL Server option in the general Tab.
In the Security Tab I selected the €œBe made using the security context€? and used €œsa€? for the Remote login and the CORRECT sa password for the With password option.
The server options tab remain the same with €?RPC, RPC Out, & Use Remote Collation€? selected.
It appears to connect but there is not any Table or Views showing up. What am I doing wrong here and what do I need to do to link these 2 database engines so I can move forward?
Thanks,
Gene Godsey
View 7 Replies
View Related
Aug 8, 2005
hi all,i have created a new login say "me" with the same userid and the password to access my database "test".I created this using the Enterprise Manager so i am not well aware of the securities and permissions in the sql server 2000.The problem is that with this login "me" i can access the master and pubs database which i dont want as i have set the permissions to access only the "test" database.I have checked this in the securties-->Logins-->Database Access tab.Any help on this pls.
View 5 Replies
View Related
Mar 23, 2001
Hi,
I have a permissions issue where I have created a database for a user and made him dbo. He wants to create a new SP in the database so he is connecting to another SQL server (where he has sa priviledge) and copying the contents of a SP.
When he creates a new SP in the database I created for him he gets the following error message:
"Error 118:Only members of the sysadmin role can specify the WITH LOG option for the RAISERROR statement."
The SP procedure is as follows:
----------------------------------------------------------
CREATE PROCEDURE up_check_audit
@errint,
@rowsint
AS
declare@db varchar(32)
declare@errchar varchar(10)
select @db = db_name()
select @errchar = convert(varchar(10),@err)
if @err <> 0 raiserror ('Error %s auditing in database %s', 18, 1, @errchar, @db) with log
else if @rows <> 1 raiserror ('Error auditing in database %s - row not inserted/updated', 18, 1, @db) with log
----------------------------------------------------------
I do not want to give this user sa priviledge to the server. What should I do ????
Thanks for any help,
Tim
View 1 Replies
View Related
