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. ...

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 the Access database and then importing it to the SQL server? Thanks in advance Cheers Bon This is somewhat easy. Basically you need to port or transfer your Access tables to a SQL DB and then link the Access DB/App. to the SQL tables on the SQL Server. If you keep the table names the same, the code etc shouldn't need altering, bu I would test it first. db55 wrote: > This is somewhat easy. > > Basically you need to port or transfer your Access tables to a SQL DB > and then link the Access DB/App. to the SQL tables on the SQL Server. > If you keep the table names the same, the code etc shouldn't need > altering, bu I would test it first. > If only it were that easy :-) Bon wrote: > 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 a...

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...

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...

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...

MS SQL Server 2000 / MS Access
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. No idea - it depends how big the tables are, what your queries look like, how you execute your queries etc. If you're using stored procedures, for example, then I would expect MSSQL to be faster, as all the processing happens on the server, and it can use indexes, caching etc. The best way to get an answer is simply to test it yourself. Simon ...

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...

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...

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...

#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. ...

Tables are skipped or export failed error occurs when upsizing MS Access to MS SQL Server
Hello All I got "Tables are skipped or export failed" error when I used upsizing wizard on migrating MS Access 2000 to MS SQL Server 2000. I am changing my application backend database from MS Access 2000 to MS SQL Server 2000. My front end is MS Access interface. When I used the MS Access 2000's Upsizing Wizard for database migration, some tables cannot be migrated. The upsizing wizard report states some tables are "Tables are skipped or export failed" and some are "updating report does not have unique idex". Could you telll me how I solve both of the problems please? My setting on upsizing wizard: check the index, validation rules, default, table relationships check the trigger radio button no, never use the timestamp create the link with the existing application Thanks in advance Cheers Bon ...

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...

Moving data from MS SQL Server 2000 or MS Access XP to UniData 5.2.9
Complete MVDBMS neophyte -I may not even qualify at the neophyte level- that needs a simple solution to move data -in batch initially...real-time eventually- from RDBMSs such as SQL Server 2000 or Access 2002 (XP) to UniData 5.2.9. Any leads would be greatly appreciated. --Ryan N. Ryan N. wrote: > Complete MVDBMS neophyte -I may not even qualify at the neophyte > level- that needs a simple solution to move data -in batch > initially...real-time eventually- from RDBMSs such as SQL Server 2000 > or Access 2002 (XP) to UniData 5.2.9. > > Any leads would be greatly appreciated. > > --Ryan N. Accuterm, look at the Excel option, and then the scripting to move it on. www.asent.com Better yet, most likely you need a consultant. Check out Tony Gravagno. He's good at building interfaces, and has most of the tools pre-built. He is at http:(no spam)//nebula-rnd.com/ get rid of the (no spam) HTH, Patrick <;=) "(Latimerp)" <"(Latimerp)"@comcast.net> wrote: >Ryan N. wrote: >> Complete MVDBMS neophyte -I may not even qualify at the neophyte >> level- that needs a simple solution to move data -in batch >> initially...real-time eventually- from RDBMSs such as SQL Server 2000 >> or Access 2002 (XP) to UniData 5.2.9. >> >> Any leads would be greatly appreciated. >> >> --Ryan N. > >Accuterm, look at the Excel option, and then t...

Configuring Access 2002 database permissions to linked tables on a SQL 2000 server.
Hi all, I recently created an Access database containing several reports for our senior managers and accounts receivable staff, the users access the reports through a command button on a switchboard form, which in turn runs macros to append data to temporary tables for generating the report. The .mdb file is sitting on a subfolder under our public share. When I run the macros the report generates without any errors or popup message. When other users run the report, they get an error popup that their integrated authentication isn't working on the SQL server. If I uncheck the integrated authentication checkbox and enter the standard SQL security username and password, everything works fine. My questions are 1) how do I configure the Access database to use standard SQL security instead of integrated for accessing the linked tables and 2) how do I hard-set the SA username/password so my bosses don't have to deal with it everytime they run the report? Thanks in advance, Andre ...

Too few parameters expected 2 error occurs after migrating "MS Access 2000" to "MS SQL Server"
Hello all I get Too few parameters expected 2 error and "The MS Jet Database engine cannot find the input table or query "myTempTablename". Make sure it exists and that its name is spelled correctly. 3078" after I migrated "MS Access 2000" to "MS SQL Server 2000" and relinked all linked tables. The front-end interface of my application is MS Access 2000. The back-end is MS SQL Server 2000. After I migrated the database and relinked all linked tables, "Too few parameters expected 2" and "input table or query "myTempTablename" 3078" error occurs. I found a article in Microsoft website. The "Too few parameter" error occurs because the limitation of the MS Access ODBC driver. If it is, any solution? Thanks in advance Cheers Bon Hi Bon, If you mean you are getting the error when you are trying to relink the tables through ODBC, then the problem could be - if your table(s) had a memo field, that sql server picked up the memo field as a varchar, and Access is trying to read a text field that exceeds 255 chars. You need to go to Query Analyzer (sql server QA) and run some queries against the offending table to see what the text length is in your varchar fields in the sql server table. And if Sql Server did pick up an Access memo field as a Sql Server Text field, ODBC can't read Sql Server Text fields. If any of the varchar fields exceeds 250 chars (for me I get errors after exceeding 250 cha...

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. 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. ...

MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Ser
MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Services, (Enterprise), Developer Edition 64 Bit, other 16,000 more CDs, [ no dongles, no activations, etc ... needed ! ] (free donge)! No time limitation! CD NR 4203 MicroSoft SQL Server 2000 4in1 CD NR 9452 Microsoft SQL Server 2000 Service Pack 2 CD NR 11 298 AppDev SQL Server 2000 Accelerated Training [3 CDs] CD NR 12 000 Microsoft SQL Server 2000 Developer Edition 64 Bit CD NR 13 424 TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1 CD NR 14 688 SQL Server 2000 Reporting Services (c) Microsoft CD NR 15 412 SQL Server 2000 Reporting Services Enterprise (c) Microsoft please send e-mail, to ola2@mail.gr, zorba@mail.gr ...

Converting SQL Server 2000 query to MS Access 2000 query
Hello! I am trying to convert a query written for SQL Server 2000 database tables, to a MS Access query. Here is what I have in SQL Server: SELECT t2.*, CASE WHEN t2.QType = '3' THEN t1.Note ELSE CASE WHEN t2.QType = '2' THEN CASE WHEN CONVERT(varchar(100), t1.ANumber) = '1' THEN 'Yes' ELSE 'No' END ELSE CASE WHEN CONVERT(varchar(5), t2.Qnumber)+'.' + CONVERT(varchar(5), t1.ANumber) = t2.SubQ THEN 'Selected' ELSE 'Not Selected' END END END AS Client_Response FROM (SELECT * FROM ClientAnswer WHERE (OrganizationID = '491') AND (InstrumentId = 'CM005')) AS t1 RIGHT JOIN (SELECT Questions.InstrumentId, Questions.Qnumber, Questions.Question, Questions.QType, CASE WHEN TEMPT.A IS NULL THEN QNumber ELSE TEMPT.A END AS SubQ, CASE WHEN TEMPT.Answer IS NULL THEN Questions.Question ELSE TEMPT.Answer END AS Answer FROM Questions LEFT OUTER JOIN (SELECT QNumber AS Q, Answer, CONVERT(varchar(5), QNumber) + '.' + CONVERT(varchar(5), ANumber) AS A FROM Answers WHERE InstrumentId = 'CM005')) TEMPT ON Questions.Qnumber = TEMPT.Q WHERE (Questions.InstrumentId = 'CM005')) AS t2 ON t1.QNumber = CASE WHEN t2.QType <> '1' THEN t2.Subq ELSE t2.QNumber END ORDER BY subq It runs perfectly on the SQL Server, howev...

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...

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...

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. ...

Access MS SQL Server 2000?
------_=_NextPart_001_01C3B4EB.13FA7080 Content-Type: text/plain; charset="iso-8859-1" Hello Guys, How can I access MS SQL Server 2000 database from Ruby? Is there a library I can use? Lennie De Villiers ------_=_NextPart_001_01C3B4EB.13FA7080-- Lennie DeVilliers wrote: > Hello Guys, > > How can I access MS SQL Server 2000 database from Ruby? Is there a library I > can use? > > Lennie De Villiers > > Check sqlrelay: http://sqlrelay.sourceforge.net/ I tested it a very short time only (I think with a MS SQL 2000, not even sure;-), so I can...

Re: Access MS SQL Server 2000?
Hello, Thank you! This page: http://sqlrelay.sourceforge.net/sqlrelay/programming/rubydbi.html Show how to use SQLRelay with Ruby. Lennie De Villiers -----Original Message----- Lennie DeVilliers wrote: > Hello Guys, > > How can I access MS SQL Server 2000 database from Ruby? Is there a library I > can use? > > Lennie De Villiers > > Check sqlrelay: http://sqlrelay.sourceforge.net/ I tested it a very short time only (I think with a MS SQL 2000, not even sure;-), so I can't say more about it. Raph ...

Unidata linked server with MS SQL 2000
Hi I am trying to connect to UniData using linked server from MS SQL. I tried using the following details, but could not get to view the tables. It comes with an error that "UVI connections to non Universe database not allowed. Details entered for creating a linked server: product name: Unidata data source: c:\ibm\ud60\demo provider string: Ardent.UniOLEDB location: myserver (my unidata server name) Can somebody help me out please, Thanks, ...

Sql Server 2000 linked server to Access 97/2000 incompatability
We are experiencing a problem with Sql Server 2000 linking to an Access 97 file. We have two machines that link to this .mdb file, and we recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. The link on this upgraded machine no longer works, giving this message: 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 a database created with a previous version of your application.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize r...