f



Running MS Access db as a Linked Server on SQL Server

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.

0
maxvalery1 (60)
9/6/2006 1:51:21 PM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

1 Replies
969 Views

Similar Articles

[PageSpeed] 18

This might help; note the table about halfway down that includes the
connection options for various sources.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

Stu

maxvalery@gmail.com wrote:
> 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.

0
9/7/2006 1:44:38 AM
Reply:

Similar Artilces:

SQL Server "Linked Server" to MS Access Database
I have a very frustrating problem. I have a situation that works fine on some computers, but not on others. I have SQL Server Express 2005 and Access 2003 loaded on 4 computers. I have set up a "Linked Server" in SQL Server linked to my Access database. I am running queries on t-SQL against the Access tables through the linked server. On all of the computers, I can do select queries and they all work great but what I need to do is run Update, Insert, Delete queries as well. On a couple of the computers, it works fine, locally and across the LAN, and a VPN as far as that goes. But on other computers I can do Select queries (also across the network) but no update queries. I get the error message "OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "securedjetls" returned message "Operation must use an updateable query." where securedjetls is the name of the linked server. The Access database on the computer that is not working is a copy of the database from the computer that is working and any of the tables that I'm trying to update have primary keys, so that's not the problem. ...

Running a MS-Access BE and an SQL-Server BE on same TS Server
What are the implications of running a MS-Access BE and an SQL-Server BE on the same Terminal Server? Assuming that the TS can handle the load are there any other issues? Thanks Patrick Patrick Fisher wrote: > What are the implications of running a MS-Access BE and an SQL-Server > BE on the same Terminal Server? > > Assuming that the TS can handle the load are there any other issues? > Thanks > Patrick You either serve the Access "front end" as a terminal server published application or you just stick an Access "back end" on it in which case the server is nothing more than a file server. Which are you talking about? Certainly in the case of the latter the added load would be minimal. If you plan on installing Access on the server and running Access apps from the server then the added load would be significant as your number of users climb. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Hello Rick We are talking about a customer who currently runs 4 TSs with load balancing running SQL Server to about 80 users, the data is held on a 5th TS, this system is running a program called scala and works at an acceptable speed accross an ADSL VPN Against my advice they have installed our Access97 Runtime using DFS and BE on one of the TSs, this across the VPN is very slow when Scala users a logged on. They have restricted the Access Runtime to on...

Possible to keep MS Access interface and migrate the MS Access to MS SQL Server?
Hello all Would it be possible to migrate the MS Access 2000 to MS SQL Server 2000? My application is using MS Access 2000 as database and as user interface such as forms. Now, I want to migrate the backend database from MS Access 2000 to MS SQL Server 2000. However, I want to keep the MS Access 2000 interface. Would it be possible? If I migrate the MS Access to SQL Server, would the queries, back-end VBA, macro, tables and forms be affected? Do I need to change the MS Access data type to SQL server supported data type? Which tool I can use to do the migration? Upsizing wizard or exporting...

MS SQL Linked Servers(Linking MySQL to MS SQL) Collation problem.
Hello, I have a problem. I've linked MySql server to MsSql, in MySql I have a table with Latvian data(character set is ucs2, ucs2_general_ci) and the problem is that when I use openquery to read data from MySQL server, some characters are not translated correctly! I receive question symbols instead of Latvian special characters. Maybe someone had this kind of problem with collation? P?vels Mihailovs (iceravenlv@gmail.com) writes: > I have a problem. I've linked MySql server to MsSql, in MySql I have a > table with Latvian data(character set is ucs2, ucs2_general_ci) and > the problem is that when I use openquery to read data from MySQL > server, some characters are not translated correctly! I receive > question symbols instead of Latvian special characters. > Maybe someone had this kind of problem with collation? Since I don't work with MySQL myself, it's difficult to say what is going on. But if MySQL has a notion of collation, and the collation is ucs2_general_ci, it sounds like MySQL returns Unicode data. One would expect that to arrive unscathed. What is you collation on the SQL Server side? The stored procedure sp_serveroption permits you to set options for linked servers, and two of them relates to collations. You could play around with these and see what happens. Please look in Books Online for details. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/techn...

insert fails
Hi, I configured a linked server in MS SQL Server v8 pointing to a remote AS/400 DB2 database. I used Client Access ODBC driver (v7), installed it from a Client Access Express v4.5. Select/update/delete seems to be working fine, I just cannot execute an insert statement. Here's the error I'm getting: update as400..U1.TB1 set a = 4 select * from as400..U1.TB1 insert into as400..U1.TB1 ( a ) values (1) ------------------------------------------------------ Server: Msg 7399, Level 16, State 1, Line 4 OLE DB provider 'MSDASQL' reported an error. The provider did not give any inf...

Oracle DB to MS Sql Server DB Replication question...via Sql Server Views??
Hi, I'm not sure if this is even possible, but I've been asked to look into replication from Oracle to Sql Server. He's the situation: We have access to a remote Oracle database, but only read access (not allowed to configure or change the db). We own a Sql Server database with Views that use a link server to query the Oracle database. With this setup, is it possible to replicate the Oracle database? Can we do a replication from the Sql Server with the Views into another Sql Server database, and if we can, will it be able to only pull the delta for subsequent replications? Any tips and hints would be helpful. Pleeease! Thanx, Doemon doemon@gmail.com wrote: > Hi, > > I'm not sure if this is even possible, but I've been asked to look > into replication from Oracle to Sql Server. He's the situation: > We have access to a remote Oracle database, but only read access (not > allowed to configure or change the db). > We own a Sql Server database with Views that use a link server to > query the Oracle database. > > With this setup, is it possible to replicate the Oracle database? Can > we do a replication from the Sql Server with the Views into another > Sql Server database, and if we can, will it be able to only pull the > delta for subsequent replications? > > Any tips and hints would be helpful. Pleeease! > I don't know the answer to the second question, nor your versions, but the first sh...

Re: Accessing legacy data via MS SQL Server linked servers
Chuck, You might talk to MBFoster. They may have a solution for you. Imagesql has odbclink/se but I'm not sure if this will do the job for you. Imagesql is available to 7.0 and 7.5 users. I have found in some cases that MSAccess does a quick job of access legacy data with odbclink/se...(but that is going thur Imagesql). JDBC, java, perl may be another solution. These have classes for image. Hope this helps Cathlene Mc Rae HP Sr Response Center Engineer. * To join/leave the list, search archives, change list settings, * * etc., please visit http://raven.utc.edu/arc...

Using MS SQL Server 2005 Enterprise Manager with MS SQL Server 2000
Hi Everyone, Most of our MS SQL Servers are still running on SQL 2000, but being I will soon be upgrading my workstation to Vista Business I'd like to install MS SQL 2005 Enterprise Manager to manage them all. When I tried to install MS SQL 2000 Enterprise Manager on Vista, it gave me a message saying it's not compatible, so I'll have to use 2005 I guess. We have LOTS of DTS scripts on our SQL 2000 server, and will 2005 EM work with these okay? Also I want to make sure SQL 2005 Enterprise Manager won't be adding to, changing, or 'upgrading' the DTS scripts or anything on the SQL 2000 servers as we still have folks using SQL 2000 Enterprise Manager. Thanks for any suggestions. I'm always leery when using cross-versions of Microsoft applications because they are often incompatible or they do squirly things that often break stuff. Thanks -- Alex Alex (samalex@gmail.com) writes: > We have LOTS of DTS scripts on our SQL 2000 server, and will 2005 EM > work with these okay? Also I want to make sure SQL 2005 Enterprise > Manager won't be adding to, changing, or 'upgrading' the DTS scripts or > anything on the SQL 2000 servers as we still have folks using SQL 2000 > Enterprise Manager. > > Thanks for any suggestions. I'm always leery when using cross-versions > of Microsoft applications because they are often incompatible or they > do squirly things that often break stuff. There isn't any Enterpr...

MS Access, SQL Server, Web server
Scenario -------- I am planning to write an application using MS Access as front-end. User enters records (mostly 15 records or less per user per day) into an Access database. Having done that, users click a button to transfer these records to a web server hosting an SQL Server database (set up by a service provider). Each record represents a job; provider will update its status when a job is done. At the end of day, user will open up the database to enquire the records status, which will be retrieved from the web server (through HTTP protocol) A service provider will probably have 2000 records/jobs per day, which will be fetched from the web server to its PC for job scheduling purposes. Question -------- Can someone advise me as to how to go about doing this ? I have read a little on WinInet APIs of MS Visual Studio 6, but I am still not sure; eg how to get SQL server to send data to client PC using HTTP. Thanks for your advice. I can't really understand the scenario here. You are going to have to explain this better. 1. Who are the users? What do they do? Are they mobile in that they are not connected to the network when they are working, like traveling salesmen? 2. Who are the service providers? I'm assuming that these are not Internet Service Providers or Application Service Providers, but rather people who work for the company and do things for customers. What kind of jobs do they do? Do they provide their services in places where they h...

MS SQL Server 2000 and MS JDBC SQL Server driver problems. (Connection timed out, followed by Error establishing socket)
I have downloaded the MS SQL Server 2000 Enterprise Evaluation edition, and the MS SQL 2000 Server JDBC driver. I have installed and configured the MS SQL Server 2000 onto two computers, which reside on the same network. (In fact, these two computers comprise the entire network). The network server (computer name=SUNCDS1), and the lone node (SUNCDS2), each have their own SQL Server 2000 instance. The SQL Server 2000 Enterprise Manager console on SUNCDS1 shows both SQL Server instances, the (local) instance, which is SUNCDS1, and also SUNCDS2. It may be worthwhile to mention that the SUNCDS2 instance on the Enterprise console does not show the SQL Server Agent as being started, but I can navigate to the SUNCDS2 databases, and display the table information. If I try to start the SQL Server Agent for SUNCDS2, I receive the message "Access is Denied". The enterprise console on the SUNCDS2 machine indeed shows the SQL Server Agent as being started. I have written a sample Java application that uses both the MS SQL Server ODBC driver and a JDBC type 4 driver (MS SQL Server 2000 JDBC) to establish a connection to the SUNCDS2 instance. This application resides on the SUNCDS1 network server. The ODBC driver will connect to SUNCDS2 instance wihout a problem. The JDBC driver does not connect to SUNCDS2. The JDBC driver will, however, connect to the local SQL Server instance that resides on SUNCDS1. Following are the code snippet to est...

How to connect MS Access form to MS SQL Server
Hello all I created an application which use MS Access 2000 as user interface and MS SQL Server 2000 as backend. If I want to get data from MS SQL Sever 2000 and display them on the MS Access 2000 form, where do I set the database connection to the form (i.e. tell the form to get data from MS SQL server database)? Thanks Bon In Windows control panel, admin tools, data sources (ODBC), create a system DSN of type SQL server and fill in the parameters for your SQL database. In Access, select file / get external data, files of type ODBC. Select machine data source, select the DSN you created and the table(s) you need. ...

Database link to a MS SQL Server 2000
Hi NG, we have to replikate some data between an oracle DB and an application with MS SQL Server. There is no possibility to load and push the data on the client mashine. We have to replicate the data on the server mashines directly. Is there a possibility to create a DB link from oracle to MS SQL Server or otherwise? thanx abbas On Thu, 9 Jun 2005 00:13:02 +0200, "Abbas" <ar.newsgroup@web.de> wrote: >Hi NG, > >we have to replikate some data between an oracle DB and an application with MS SQL Server. > >There is no possibility to load and push the data on the client mashine. We have to replicate the data on the server mashines >directly. > >Is there a possibility to create a DB link from oracle to MS SQL Server or otherwise? > >thanx >abbas > Look into using Heterogeneous Services in your Net administrators manual. -- Sybrand Bakker, Senior Oracle DBA Are you willing to install a bridge program to create this "link"? If so, then try www.examinstudio.com Do as Sybrand told you, look at the Heterogenous services of Oracle. Oracle even supports natively DRDA towards a DB2 z/OS mainframe, and they also support SQL Server. They all end-up like a database link with a totally quallified names in the "from" clause. Be aware of that distributed joins between different DBMS systems might cause huge performance problems, keep the functionality when dealing with multiple DBMS systems simple, no dist...

ODBC connection
Hello all! I have such question to all of you. I have some tables linked from MS SQL Server 2000. Is time of processing query based on these linked tables from MS SQL Server 2000, faster or slower than the time of processing the same query based on tables, which are not linked but imported to MS Access? Can you answer this question? Thank you in advance for posts Marcin from Poland. On Tue, 19 Apr 2005 17:23:48 +0200, "Kamyk" <marcinzmyslowski@poczta.onet.pl> wrote: >Hello all! > >I have such question to all of you. >I have some tables linked from MS SQL Server 2000. Is time of processing >query >based on these linked tables from MS SQL Server 2000, faster or slower than >the time of processing the same query based on tables, which are not linked >but >imported to MS Access? > >Can you answer this question? > >Thank you in advance for posts >Marcin from Poland. > That depends on a lot of things... The most obvious thing it depends on is the size of the result set. In a client/server arrangement, you want to keep result sizes down, and do aggregation and filtering on the server. Next, how many people will be sharing the back-end data. Access slows down as soon as 2 people are sharing a JET back-end, but the server won't have this limitation. On the other hand, if the server is too slow, or you're trying to use MSDE with more users than it's optimized for, the server will be slow. There are m...

Permissions Issue when linking to MS SQL 2000 DB from MS Access XP
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 ed...

what is equivalent of format(date) function of MS Access in MS Sql Server 2000
Hi All, I am facing a problem with a sql what i used in MS Access but its not returning the same result in MS Sql Server 2000. Here i am giving the sql: SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHERE MY_ID=1 The above sql in ACCESS return me the date in below format in one column: Friday 09, 2003 But in Sql server 2000 i am not getting the same format eventhough i am using convert function, date part function etc. Please if you find the solution would be helpful for me.. Thanks Hoque Hi If you field is a datetime, you can use the CONVERT function to conver...

Adding records to a SQL Server database from MS Access
Thanks in advance for your help. I have just upsized an Access database to SQL Server with the hope of using a fairly extensive Access application that was part of it. But now, the forms will not allow the addition of new records (the >* button is greyed out). The forms use a macro to advance the dataset to a new record. How can I get the additions (inserts) turned back on? Regards. ...

General advice needed regarding MS Access, MS SQL Server, MySQL/PostgreSQL
I am working on two versions of an application, one of which will be a windows forms application (which will need to be redistributable) and the other will be a web application. I have MS Visual Studio 2005 (along with the developer's edition of MS SQL Server), but not MS Access. I also have MySQL, PostgreSQL, Sun's application server, Tomcat and Apache web server. I am working on Windows XP Pro, and have installed the .NET 3 SDK and all relevant related products I could find (e.g. 2 extensions packages for Visual Studio). I have one MS Access database, to which my users should have read only access. I have, and have used, a tool for importing MS Access databases into MySQL. I expect that SQL Server has a similar utility hidden somewhere (where I haven't yet looked, though I HAVE been looking - obviously in the wrong places). I have located a similar utility for importing MS Access databases into PostgreSQL. I have not yet decided which servers to use for the web version, but that is another story, for which I may raise another thread in due course (but I welcome suggestions which may reduce the effort required given required effort for the windows forms app). My problem is for the windows form aplication (intended for use by a single family). I expect to use ADO.NET. The question is, should I import the Access database into MS SQL, and redistribute it, along with MS SQL Server Express (or is that necessary), or distribute it just as an Access database a...

#Deleted In All Fields When Linking SQL Server Table In MS Access
I have a set of tables in SQL Server, which, when I link in Access, I get all the fields displayed as #Deleted when I look in table view or the output of a query. However, when I walk the recordset in code, it is just fine, and it displays correctly in Enterprise manager and Query analyzer. Looking back in the archives there is some indication of a problem with respect to autonumber verses IDENTITY fields, however, even if I do SELECT * FROM Table it shows the same output, or if I make it purely read only, such as SELECT Name & ProductID as Info FROM Products, I still get the same. The code that walks the table correctly is: Dim db As Database Dim rs As Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("dbo_Products") Do Until rs.EOF Debug.Print rs!ProductId, rs!Name rs.MoveNext Loop rs.Close I'd appreciate any suggestions or recommendations to fix this problem. Thanks in advance. ...

Re: Accessing MS SQL server or ORACLE database from the HP3K
Folks, One small clarification to my previous question: We are not trying to migrate our IMAGE data to another platform. We are trying to provide an enhancement where data that already exist in a database on another platform can be viewed, extracted and used to "complete" an existing entry in the IMAGE database, thus eliminating the need for the user to manually 're-enter' the data. Regards Paul Christidis =============================================================================== Folks, I was asked the following question today and decided to turn to y...

Php and DataBase Abstraction for mySql, Sql Server, and MS Access?
Is there a good DataBase Abstraction layer that will handle the mySql, Sql Server, and MS Access engines? I do not wish to write the same functions with the three different PHP db functions. I see that php_dba is listed as an extension, but I determine which DBs are supported. However, I think that it does not support the ones I need. Thanks David wrote: > Is there a good DataBase Abstraction layer that will handle the > mySql, Sql Server, and MS Access engines? I do not wish to write the > same functions with the three different PHP db functions. > > I see that php_...

Access MS-ACCESS database on server from client????
I m currently developing a java based application and want to know how can i make client access database located on the server? "HeMan_Speaks" <Lunar20092010@gmail.com> wrote in message news:d71ce567-0434-48b8-b1d2-bb191706ab45@w8g2000prd.googlegroups.com... >I m currently developing a java based application and want to know how > can i make client access database located on the server? I think you're going to have to expand a little bit more in your question here, as is not 100% quite clear what you're trying to do. You might want to make a little bit of a distinction between MS access, the development system that allows you to write code, build forms, and build reports, and that of the database engine that you choose to use with MS access. When you build an application MS access, you then choose your database engine, that might be oracle, SQL server, or perhaps more often leave the default jet database. So when you say make a client access application, are you talking about a piece of software that you plan to install and each computer? The fact that your introducing the issue of java further complicates your question. Perhaps your question is simply you have some java code running on a server, and with to open a mdb file (an access database file). In this situation you're not really using MS access at all here (you using what is called the jet database engine to read that mdb file). For all the trouble in this type of sce...

Re: Accessing MS SQL server or ORACLE database from the HP3K #3
In a message dated 7/8/03 5:23:24 AM Pacific Daylight Time, pete@bookware3000.ca writes: > It's not MS SQL or Oracle but for what it's worth, we once had a Cobol > program > accessing a PostgreSQL database that resided on a Linux server by using the > C > libraries included with Mark Bixby's PostgreSQL port. > I for one would absolutely love to see an example of that! Sometime soon (?) I hope to be able to finally spend some time working with PostgreSQL on MPE and/or Linux. Having a known working example of how to access it via COBOL is a great ...

Re: Accessing MS SQL server or ORACLE database from the HP3K #3
Paul, if nothing else helps, you could use Java and JDBC with the JDBC driver of the respective target database. A small example showing access to an Image/SQL database can be found on the following web page (and can be adjusted to use an MSSQL or Oracle JDBC driver and database URL/logon fairly easily)... http://www.editcorp.com/Personal/Lars_Appel/JavaDemo/ -> JdbcProgram Calling Java from COBOL as a subprogram, however, is probably not the way that I would go, if exploring this road. I would probably let a program similar to the above run as a "gateway" in a sep...

Re: Accessing MS SQL server or ORACLE database from the HP3K #6
On July 9, 2003 11:23 am, Duane Percox wrote: > * Performance was not that great so you wouldn't want to use this > for production, but proof of concept and for learning it is ok. Do you have any performance numbers for it all on Linux? Pete * To join/leave the list, search archives, change list settings, * * etc., please visit http://raven.utc.edu/archives/hp3000-l.html * ...

Web resources about - Running MS Access db as a Linked Server on SQL Server - comp.databases.ms-sqlserver

Running - Wikipedia, the free encyclopedia
This article is about the type of locomotion in humans. For running in horses, see Horse gait . For locomotion in dogs, see Gait (dog) . For ...

Malcolm Turnbull is running on my government's record: Tony Abbott
Former prime minister Tony Abbott has declared the government will head to the election fundamentally unchanged under Malcolm Turnbull, hours ...

Malcolm Turnbull is running on my government's record: Tony Abbott
Former prime minister Tony Abbott has declared the government will head to the election fundamentally unchanged under Malcolm Turnbull, hours ...

How Valve got passable VR running on a four-year-old graphics card
That green box in the middle of this Aperture Science scene indicates it's running right in the middle of the adaptive performance curve. (credit: ...

Do You Want To Help Put A NeoCon Administration Back Running The White House?
Hillary Clinton's mindset, borne out by her record, is extremely dangerous to world peace. Democratic voters should understand who she really ...

Soros Board Member Chairs Firm Running Online Voting for Tuesday’s Utah Caucuses
Smartmatic Group, an electronic voting firm whose worldwide headquarters is located in the United Kingdom, will be running the online balloting ...

The CEO of $300 million company Refinery29 reveals what it was like running a media startup back when ...
... "So what do you do when you don't do this?” It was like, 'Where's this gonna go, is this like, your hobby?' Shontell: Before you were running ...

Official: ISIS' money running short because of U.S. bombing campaign
New report shows that over the past 15 months, ISIS has also lost more than a fifth of the territory it controlled in Iraq and Syria

Khloe Kardashian: It’s ‘f–king awesome’ that a woman is running for POTUS
Here are some photos of Khloe Kardashian at 1Oak at the Mirage last Friday. She was hosting a birthday party event, and I’d assume she was being ...

Chancellor is running out of options for budget surplus plan
Chancellor George Osborne is running out of room to keep his centrepiece budget surplus plan on track, economists said on Thursday.

Resources last updated: 3/22/2016 3:30:31 AM