Running MS Access Db As A Linked Server On SQL Server
Sep 7, 2006
Hi,
Looking for a quick and dirty on running an Access database as a linked
server in SQL Server. Basically, the majority of my stuff is in SQL
Server, but there is one lingering nightmare-of-an-Access-database no
one wants to touch.
I just want to create a linked server to use the Access db.
So far I have not found a way to connect (under Security -Link
Servers in SQL Server), though I tried all kinds of drivers, connection
strings, etc. What do I put for Product Name, Data Source, Provider
String, (Location, Catalog)?
Thanks a bunch.
View 1 Replies
ADVERTISEMENT
Mar 25, 2002
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 5 Replies
View Related
Aug 27, 2007
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======
On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
=======
My resolution that generates the second error message
=======
On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.
View 1 Replies
View Related
May 23, 2001
I have created a linked server using an ODBC connection to an Access database. The command I used for this was:
EXEC sp_addlinkedserver
@server = 'Testaccess',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'test'
GO
where 'Testaccess' is the name of the linked server and 'test' is the name of the ODBC connection. [The ODBC connection stores the name of the Access datbase.]
I can run sp_tables_ex and view the table_schem, table_cat etc etc
BUT when I try to run a query on the linked server, I get the following message:
"7312 - Invalid use of schema and/or catalog for OLE DB provider '%ls'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema."
I am attempting/using the following command specifying the linked server name.database name.owner.table name in the FROM clause:
select a.*
from testaccess.testdb.dbo.temptable a
Any ideas what I am doing wrong??
thanks
Jan
View 1 Replies
View Related
Dec 30, 2002
I have a stored procedure that references another stored procedure on a linked server. I would like to check if the linked server is running prior to accessing the stored procedure. Everything I have tried returns either message 11 or 17 and terminates the stored procedure. I would like to do some cleanup before the termination. Is there any way to check this?
ben
View 2 Replies
View Related
Aug 2, 2006
i have set up
a linked server. i can query the linked server in query analyzer and
also do update/delete. but when i try to run the same query for linked
server through insert trigger, i get following error: [OLE/DB provider returned message. [Microsoft][ODBC Sql Server Driver]Distributed transaction error].btw, i am using Sql server 2000, SP4. main server is windows 2003 server and linked server is windows xp pro.any suggestions will be appreciated.
View 1 Replies
View Related
Feb 26, 2007
I'm trying to create linked server to access DMX functions from SQL Server as per:
Executing prediction queries from the relational server
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx
I create the link this query
EXEC master.dbo.sp_addlinkedserver
@server = N'KLSSQL01AnalysisServerLink',
@srvproduct=N'Analysis Services 2005',
@provider=N'MSOLAP',
@datasrc=N'kls-sql01',
@catalog=N'AnalysisServicesPredictorPrototype'
GO
SELECT * FROM OPENQUERY(KLSSQL01AnalysisServerLink, 'select node_caption, node_type from [Misuse Abuse Profile].content')
where [Misue Abuse Profile] is the Mining model
Provider options: Allow in process
I receive the follwing error:
OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink" returned message "An error was encountered in the transport layer.".
OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink" returned message "The peer prematurely closed the connection.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink".
I found this post but there was no resolution.
run openquery(mdx) through a linked server
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=924869&SiteID=1
View 2 Replies
View Related
Aug 30, 2006
I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.
View 4 Replies
View Related
Jul 16, 2015
We are upgrading from SQL 2008R2 to SQL2014 but we have discovered that a couple of our applications are not supported on 2014. We'd like to keep one 2008R2 server and one 2014 server until we have time to upgrade the applications and move everything to the new server. The problem is we have custom code in some of the 2014 databases that access tables in the 2008 databases.
I know we can easily do cross server joins by using a linked server, but it would be a huge undertaking to find all that code and add a linked server name in front of every table, stored procedure, etc. So my question is, is there any way to move a database to a different server and still be able to access it without having to qualify the object names with a linked server? Is there some kind of server/database synonym that can be setup that would be recognized by all databases?
View 8 Replies
View Related
Jun 20, 2006
I have two sql server 2000 with mixed mode authetication. I stand at one server and setup a linked server to the other using the same user id and password.
However when I click to the Tables icon of linked server in Enterprise manager, there is an error message:
Error 17 SQL Server does not exist or access denied.
And It does not show any table.
I register remote sql server in Enterprise manager fine.
Could any one help me ?
Thanks.
View 14 Replies
View Related
Feb 9, 2006
An Access 97 linked server in SQL Server 2K has recently stoppedworking. In Enterprise Manager, the following error message appearswhen attempting to view the list of tables in the linked server:Error 7399:OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].This linked server has not been used recently and in that time Win2KSP4 and SQL Server 2K SP4 have both been applied. We now need to usethis linked server on a regular basis so any help will be muchappreciated.
View 4 Replies
View Related
Jun 27, 2001
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
View 2 Replies
View Related
Nov 5, 2001
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
View 1 Replies
View Related
Oct 5, 1999
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 Related
Feb 9, 2007
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.
View 6 Replies
View Related
Jan 11, 2002
Hi,
I doing Coldfusion web development using MS Access, but the final product needs to use SQL Server. Is there a way for me to upsize from MS Access to SQL Server without having to purchase/install SQL Server?
Thanks.
View 3 Replies
View Related
Apr 23, 2008
Hello,
I am trying to create a linked server in a SQL Server 2000 instance that is linking to a SQL Server 7 instance on an NT4 box. I am able to link the server but when I try to run a query (create a view) I get an error. It is the same error that I got when accessing linked servers on SQL Server 2000 instances running on Windows Server 2003. In those cases i just had to follow this procedure to get it to work:
1.
Click Start, point to Control Panel, and then click Add or Remove Programs.
2.
Click Add/Remove Windows Components.
3.
Select Application Server, and then click Details.
4.
Select Enable network DTC access, and then click OK.
5.
Click Next.
6.
Click Finish.
7.
Restart the computer.
However, this is obviously not the same in NT4 on SQL Server 7. My question is how do I enable network DTC access on this system so that I can create and query the linked database?
Thanks!
View 3 Replies
View Related
Jul 16, 2001
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?
View 1 Replies
View Related
Jan 6, 2005
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
View 3 Replies
View Related
Apr 5, 2006
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
View 3 Replies
View Related
Jan 13, 2004
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.
View 2 Replies
View Related
Oct 16, 2007
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
View 1 Replies
View Related
Aug 13, 2007
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 Related
Nov 8, 2006
windows 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".
View 3 Replies
View Related
May 22, 2007
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.
View 3 Replies
View Related
Dec 13, 2004
Hi,
Can someone help me with this problem.
I have a stored procedure in SQL Server that updates a particular table. When I run it in SQL server Query Analyser, it works fine. But I want to invoke this stored procedure when I click a button on an MS Access Form. The code I'm using is:
Dim cn, cmd
Set cn = CreateObject("ADODB.Connection")
cn.Open "SQL" //Data Source Name
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "LoadApplicants" //Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Execute
for some reason only a few records are updated everytime I click on the button. Is there any reason why this is happening?
View 4 Replies
View Related
Feb 5, 2008
Hello:
I have a package which runs fine from the client but not the server (see error below). I am logged into the client with my domain account, and the package runs under a proxy using that same domain account (for now). The MS Access db I am trying to pull from is on a non-domain computer, and I have created a Y: mapped drive on both the client and server that access a share on that machine where the Access database lives--I created the mapped drive using the "different user" option, and used a local account on the non-domain computer to create the mapped drives.
On the server, when logged in as my domain account, I can use the y:MyDB.mdb you can see below in Start | Run to hit the Access database. Why can't the package also see this file when running on the server?
Code Snippet
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "'y:Mydb.MDB' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.". End Error Error: 2008-02-05 02:35:56.26 Code: 0xC020801C Source: Cost Cost [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "MyDB" failed with error code 0xC0202009. There may be error messages posted before this with more informat... The package execution fa... The step failed.
Thanks,
Kayda
View 10 Replies
View Related
Mar 16, 2007
Could anyone shed some light on the syntax of accessing system function on a linked server?I'm trying to get the recovery models of databases on a linked. However using databasepropertyex locally generates wrong results.e.g. select databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases I tried select [server/databasename].databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases which does not work. Thanks.
View 1 Replies
View Related
May 11, 2000
Hi:
No matter how I configure my link going thru EM I get a 7399 or 7303 error. First I tried to link to an Access 2000 db on a Windows 98 station. Next I installed Access 2000 on the test server with SS7, SP 2 (Windows NT 4, SP 5) and still the same errors. Is it possible to set up Access 2000 as a linked server on SS7? Can you give me the brainless man's walk-thru so I can get over this hump?
Thanks in advance,
Gary
View 3 Replies
View Related
Jun 7, 2000
Hello,
We are currently live with a CRM solution (Siebel) that uses SQL Server 6.5 as the back end. All is fine and dandy, except I have some reservations about security.
Quite simply, it is possible for anyone to open up MS Access and link to any of the SQL Server database tables via the ODBC DSN used by the Siebel front end. This DSN is necessary for Siebel to function.
I am bit worried that someone (out of incompetence or spite) might do just that and cause some serious damage. Its probably technically beyond the large proportion of our users (especially those that could make mistakes!), but I can't get the nagging fear out of my head.
Does anyone know of anyway to combat this problem? I have scoured the web, including this site, and cant seem to get any information on this.
Thanks and Regards
Dike
View 2 Replies
View Related
Oct 16, 2000
Hi all,
I have built several Linked Servers from my SQLServer 7 database to some Access 97 MDB files sitting on a Novell file server. When I am physically sitting at the SQLServer I can access/modify data through the links just fine. I have successfully built views, stored procs and jobs referencing these links and they all work perfectly ... As long as I am sitting at the SQLServer. So if I execute 'Select * from RT1...Emp' I get data back.
Now my problem. If I connect to the server from Query Analyser on a different machine I can not execute and anything that references a linked server. If I execute 'Select * from RT1...Emp' I get.
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: DIsk or Network error.]
I want to be able access various views and stored procs via a VB app so. Eventually I will be moving all of the Access data into the SQLServer but for now I must access the data via links.
Any ideas?
Mark Armer
mark@armerville.com
View 2 Replies
View Related
Mar 23, 2006
is it possible to define in sql server linked tables to odbc data sources as it is possible to do in access?
except Access and vfp, is there another database that allows to create linked tables to odbc data sources?
I need to combine in a database native tables with odbc linked tables, but it would need to use a database more powerful that access or vfp
View 2 Replies
View Related
Jul 20, 2005
We recently translated the backend db from Access(97) to SQL Server.We are still using Access frontends. I have an update query in theAccess front end that uses a lookup table to populate fields. Thecommon fields between the table and the lookup table are the primarykey (LocID) and date & time fields. The query is:UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],tblPT.Salinity = [tblPT_Offsets].[Salinity]WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And[EndDate]));This worked fine in Access and seemed to work fine after switching toAccess, but on closer look, there is exactly a 2 day error beingintroduced. A quick search of the newsgroups brings up lots of Accessto SQL date problems, but a 2 day offset seems rather strange? Anyideas??I know the field names Date and Time are inappropriate, but legacyissues are a pain in the butt to resolve!! Could this be a problem?David
View 3 Replies
View Related
