f



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

0
10/31/2005 11:34:19 AM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

4 Replies
3141 Views

Similar Articles

[PageSpeed] 0

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.

0
chfran (32)
10/31/2005 10:17:38 PM
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 :-)
0
nospam4428 (164)
11/1/2005 8:09:08 AM
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 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?

Queries are one thing you'll need to look at. In the first instance 
Access may do a direct translation to T-SQL (if you're lucky) otherwise 
it can prepare a load of SPs and execute them, sometimes this works and 
sometime it results in it effectively bringing across the entrire tables 
from SQL Server and performing joins locally, which is bad. YMMV.

If you're moving queries to views, take care if the query uses any built 
in or VBA functions as these won't exist in T-SQL. You can either write 
a UDF or write the query differently.

In Access the use of "where exists (select...)" performs very badly 
compared to "where column in (Select column...)", in SQL Server the 
exists method is more efficient.

DAO code may behave unexpectedly, e.g. I had this problem 
(http://www.besty.org.uk/memory.htm) but that's since been fixed and I 
can't reproduce it now. Using ISAM methods in DAO (.Index, .Seek) will 
not work.

Data-types shouldn't be a problem, SQL Server has more than Access, 
Access will assume its own types when it sees them on the server. A few 
caveats apply:

Access Yes/No is equivalent to SQL Bit but make sure you make it 
required and default to 0 else unpredictable results can occur. Also 
realise the values are different, in SQL Server its 0 and 1, in Access 
its 0 and -1 and most of the time translates OK but if using an Access 
query its better to use <>0 as criteria than =1 or =-1 or =True just to 
be on the safe side.

Datetime data.
SQL Server only allows back to 1753 or some such, Access allows 
(incorrectly as it doesn't handle missing days) further back than that. 
You may think this might not concern you but the number of people who 
put in 1/12/202 instead of 2002 will cause you immediate problems. If 
you have that situation you have a problem already but just not realise it.
Also datetimes are stored differently in each and floating point errors 
can occur resulting in the dreaded "#deleted" appearing in rows where 
this occurs or "data has changed" errors. A timestamp column in the 
table cures this.

> Which tool I can use to do the migration? Upsizing wizard or exporting
> the Access database and then importing it to the SQL server?

I've not come across a perfect one but in the past I've used the 
upsizing wizard as it upsized more in the way of indexes, relationships, 
etc.
0
nospam4428 (164)
11/1/2005 8:32:25 AM
On Tue, 01 Nov 2005 08:09:08 +0000, Trevor Best <nospam@localhost.invalid>
wrote:

>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 :-)

What Trevor is getting at is that it's easy to take an average Access app,
change the tables into links to tables on the server, and have it technically
function.  Unfortunately, many of the design decisions commonly built with a
JET back end are not appropriate for a C/S app, and perform dismally in that
context.
0
nospam48 (2784)
11/1/2005 8:36:34 AM
Reply:

Similar Artilces:

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

Create MS/ACCESS database app for non-ms/access users
Is it possible to create an MS/ACCESS database application and package it for users who do not have MS/ACCESS loaded in their PCs? I was told that this is possible, but I don't know what software is needed. Do you know? If so, please answer this post. Thanks. SueB *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Per Sue Bricker: >Is it possible to create an MS/ACCESS database application and package >it for users who do not have MS/ACCESS loaded in their PCs? > >I was told that this is possible, but I don't know what software is >needed. Do you know? If so, please answer this post. Thanks. I think it comes down to semantics. "Licensed" vs "Loaded". No you can't create an app that will run on a PC with nothing loaded. Yes you can create an app that will run on a PC where nobody's paid the fee to run MS Access. You still have to install something on that PC, however. What it is is a freebie version of MS Access that only runs applications - doesn't allow anybody to develop an app. You develop the app on your PC, which has MS Access installed. Then somebody installs the freebie ("Runtime Access") on somebody's PC and they can run the app you developed without having to pay any money to anybody. -- PeteCresswell Sue, If Access isn't present, some version of it must be installed to run an Access database. VB.NET, ...

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

MS Access conversion to MS SQL
Hi, I've been developing a database for a department in a local organisation. The database is in Microsoft Access and uses tables, queries, forms and reports. Written into the forms is vast amounts of visual basic. There are also a couple of macros. In the time I've been developing this database (a year now, as I went back to university, and then finished university and returned to finish off the DB) the organisation I've been working for have decided to start using MS SQL. The department I am working for aren't to happy as not only my database but most of theirs are in Access and no one seems to have actually used SQL or know anything much about it. Basically, I need to learn about how to convert from Access to SQL. I understand we use the upsizing wizard. I've managed to track down some of the rules I have to use in the conversion (like no spaces in names, no apostrophes etc), but I'd like to know if anyone could tell me more about the upsizing procedure. I'm also concerned about the vast amounts of forms I've made (It's a 109mb database with no data - all down to the forms!). What happens to these? Does SQL use forms, or will all of these be lost? And what about queries? Any information from someone who has knowledge of these things would be greatly appreciated! Many thanks, Stu. Hiya, An account of how I did it can be found at: http://www.cooldigitec.co.uk/NJW/15-AccessToSQLServer7.html Basically, 1. Close all database objects...

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

Should I upgrade from MS Access 97 to MS Access 2002
Hi guys My company has in one of their products used Access 97 and MS JET 3.5 for several years now. Should we move the database to Access 2002 -which we use now in other parts of the company and also upgrade the MS JET to the latest version? And what does MS JET do? Kjell Arne Johansen MS Jet is the database engine that comes with, and is, by default, used by Microsoft Access. If you aren't having any troubles with the Access 97 database, then there isn't any technical reason to change. There may be company political reasons if, say, the IT department only wants to have one version of Access in-house. Another reason might be if you were concerned about getting support (error fix support) from Microsoft. Then again, when is the last time you had to call Microsoft support for this database? Larry Linson Microsoft Access MVP "Kjell Arne Johansen" <kjell.arne.johansen@kongsberg-simrad.com> wrote in message news:3f2f8600.699258150@news.eunet.no... > Hi guys > > My company has in one of their products used Access 97 and MS JET 3.5 > for several years now. > > Should we move the database to Access 2002 -which we use now in other > parts of the company and also upgrade the MS JET to the latest > version? > > And what does MS JET do? > > Kjell Arne Johansen "Kjell Arne Johansen" <kjell.arne.johansen@kongsberg-simrad.com> wrote in message news:3f2f8600.699258150@news.eunet.no... > Hi gu...

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

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

Date picker from MS-Access 2000 to MS-Access 2003
Hello. How do I get a date picker (References: Microsoft Calendar Control 8.0 Location C:\Windows\System 32\mscal.ocx) inset in MS-Access 2000, to work in MS-Access 2003. TIA J�rn Why not scrap it and use the all-API approach Stephen Lebans has at http://www.lebans.com/monthcalendar.htm -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "JHB" <joern_bea@removeThis_tdcadsl.dk> wrote in message news:42190d18$0$48635$edfadb0f@dread15.news.tele.dk... > Hello. > > > > How do I get a date picker (References: Microsoft Calendar Control 8.0 > Location C:\Windows\System 32\mscal.ocx) inset in MS-Access 2000, to work > in > MS-Access 2003. > > > > TIA > > J�rn > > Allen Browne has an excellent calendar control at http://allenbrowne.com/tips.html -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com www.pcdatasheet.com "JHB" <joern_bea@removeThis_tdcadsl.dk> wrote in message news:42190d18$0$48635$edfadb0f@dread15.news.tele.dk... > Hello. > > > > How do I get a date picker (References: Microsoft Calendar Control 8.0 > Location C:\Windows\System 32\mscal.ocx) inset in MS-Access 2000, to work in > MS-Access 2003. > > > > TIA > > J�rn > > Thanks for the link...

conversion of ms access 97 application to ms access 2000
hi access gurus would appreciate if u can give me pointers regarding conversion of ms access 97 application to ms access 2000, like what are the problems to be expected and how to handle it. also would appreciate if anyone can give pointers on using VSS with ms access 2000, how to handle multiuser issues when it comes to insert, update and delete. thanx in advance, regards bala balkiicbe@yahoo.com (bala) wrote in message news:<b62a781b.0310080544.6d2a08fc@posting.google.com>... > hi access gurus > > would appreciate if u can give me pointers regarding conversion of ms > access 97 application to ms access 2000, like what are the problems to > be expected and how to handle it. > > also would appreciate if anyone can give pointers on using VSS with ms > access 2000, how to handle multiuser issues when it comes to insert, > update and delete. > > thanx in advance, > > regards > bala About the only thing that's seriously different when you upgrade from 97 to 2000 is that if you have any code behind your forms, 97 uses DAO and 2000 uses ADO, so the ADO library is registered by default in 2000. You'll just have to unregister it and register DAo 3.6 instead. Access should do the conversion for you... hi Pieter thanx for the information, well have used ADO in ms access 97 so i think the conversion shouldn't be a problem. take care and best of luck regards bala ...

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

MS SQL to MS Access Stored Proc. for Deletes
I have this stored procedure below that works great in SQL Server. The sql purges rows out of a table, leaving "x" # of rows in the table ("x" = s.RetainHistoryNum below). Now I need it to run in MS Access. The thing that throws me off is the multiple "from" statements. I have tried a dozen different queries, nothing seems to do the trick. Either I end up with an syntax error, or I end up with inaccurate results. Dan -------------------------------------------------- -Here is the stored proc I need to work in Access -------------------------------------------------- DELETE FROM ScheduleHistory FROM Schedule s WHERE ( SELECT COUNT(*) FROM ScheduleHistory sh WHERE sh.ScheduleID = ScheduleHistory.ScheduleID AND sh.ScheduleID = s.ScheduleID AND sh.StartDate >= ScheduleHistory.StartDate ) > s.RetainHistoryNum ---------------------------- -Here are the tables: ---------------------------- CREATE TABLE Schedule ( ScheduleID COUNTER CONSTRAINT PK_ScheduleID PRIMARY KEY, TypeFullName TEXT (200) NULL , TimeLapse INTEGER NULL , TimeLapseMeasurement TEXT (2) NULL , RetryTimeLapse INTEGER NULL , RetryTimeLapseMeasurement TEXT (2) NULL , RetainHistoryNum INTEGER NULL , AttachToEvent TEXT (50) NULL , CatchUpEnabled BIT NULL , Enabled BIT NULL , ObjectDependencies MEMO NULL); GO CREATE TABLE ScheduleHistory ( ScheduleHistoryID COUNTER CONSTRAINT PK_ScheduleHistoryID PRIMARY KEY, ScheduleID INT...

JDBC+Linux -> MS Access/MS SQL??
I want to use JDBC to connect a remote MS Access/MS SQL database, however, I found that JDBC-ODBC is not support on remote database and Linux. I've visit the site of some middleware, like RmiJdbc, IDS,.... However, seems don't know how to make it functions and difficult to configure. Do anyone can give me a solution? Thanks Vincent You can try the jTDS driver (http://sourceforge.net/projects/jtds), performs really good. Or you can buy some other jdbc driver for MS SQL Server. /Klas "Vincent Ho" <vincenthkh@hotmail.com> wrote in message news:fb01e579.0308061746.3bf10a6b@posting.google.com... > I want to use JDBC to connect a remote MS Access/MS SQL database, > however, I found that JDBC-ODBC is not support on remote database and > Linux. > I've visit the site of some middleware, like RmiJdbc, IDS,.... > However, seems don't know how to make it functions and difficult to > configure. > Do anyone can give me a solution? > > Thanks > Vincent Vincent Ho wrote: > I want to use JDBC to connect a remote MS Access/MS SQL database, > however, I found that JDBC-ODBC is not support on remote database and > Linux. > I've visit the site of some middleware, like RmiJdbc, IDS,.... > However, seems don't know how to make it functions and difficult to > configure. > Do anyone can give me a solution? > > Thanks > Vincent Accessing SQL server from ...

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

left() and right() function in MS SQL vs MS ACCESS
I normally use MS ACCESS vs MS SQL,, which has a left() and right() function. I need to use MS SQL for this project but I am not familiar with it. I have read a few books, but can not figure out how to do this. Please help. If I need to compare the first 4 letters of a field, with the first four letters of another field, how can I do this? Select field1, field2 FROM table1 Where left(field1,4)=left(field2,4) (MS SQL does not have left() and right() functions) Please help. In addition, I have a CSV file with data like 10.20, which I import inrto a numberic field. Unforunately the value gets changed to 10. It's seems to get rounded. How can I fix this. The import SQL I use is.... BULK INSERT dbo.table FROM 'c:\MYDATA.CSV WITH ( FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) Thank you in advance!!! On 29 Jun 2006 11:40:22 -0700, "sql guy123" <stoppal@hotmail.com> wrote: >Select field1, field2 FROM table1 Where left(field1,4)=left(field2,4) > >(MS SQL does not have left() and right() functions) MS SQL Server does have both LEFT and RIGHT functions. From the Books on Line for SQL Server 2000: RIGHT Returns the right part of a character string with the specified number of characters. Syntax RIGHT ( character_expression , integer_expression ) Arguments character_expression Is an expression of character or binary data. character_expression can be a constant,...

Use ASP to read MS Access query that calls MS Access function
Hi: I have an MS Access query for a table called MyTable. One of my fields in the query is a custom field that looks like "MyField: = MyFunction(ID)" where ID is a field in MyTable. I have ASP code and I can use it to read the query OK when "MyField" is not part of the query. If I add in MyField and then run my ASP code to try to read all the query fields, I get an error message on my web browser which says something to the effect of "cannot find MyFunction." On the Access side, I have MyFunction set up in a module and use the keyword "Public" in front of the function name. What else do I need to do to get my ASP code to run this query. I'm trying to avoid reproducing MyFunction within the ASP code if possible. Thanks. Kevin On Thu, 14 Jun 2007 21:38:19 -0000, k-man <kmccrack@gmail.com> wrote: Sorry, no can do. From ASP you don't have access to the Access VBA interpreter. -Tom. >Hi: > >I have an MS Access query for a table called MyTable. One of my >fields in the query is a custom field that looks like "MyField: = >MyFunction(ID)" where ID is a field in MyTable. > >I have ASP code and I can use it to read the query OK when "MyField" >is not part of the query. If I add in MyField and then run my ASP >code to try to read all the query fields, I get an error message on my >web browser which says something to the effect of "cannot find >MyFunction."...

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

Accessing a MS Access database across two servers
Hi The technical support guys at my company have set up my system so that the server containing the MS Access database is on one server, while the pages that should access the database are on another server. When the pages and database are on the same server, I usually just connect through ODBC. The problem is that I don't know how to set up an ODBC connection to link from one server to a database in another. Anyone know what I am getting at and know of a possible solution, or a link to a site which may help. thanks in advance Brendan "Singularity" <Brendan.Collins@Singularity.co.uk> wrote in message news:1118749208.204709.287950@g49g2000cwa.googlegroups.com... > Hi > > The technical support guys at my company have set up my system so that > the server containing the MS Access database is on one server, while > the pages that should access the database are on another server. > > When the pages and database are on the same server, I usually just > connect through ODBC. > > The problem is that I don't know how to set up an ODBC connection to > link from one server to a database in another. > > Anyone know what I am getting at and know of a possible solution, or a > link to a site which may help. > I think this may be more of a network issue than an Access one. Can you see both servers from your PC? Is there any particular reason why they've been put on different servers? Seems a little odd to...

copying data from MS-SQL to MS-Access using VB.Net
Hi there, I'm trying to use MSAccess as a "container" to move data around from one MS-SQL server DB to another. This is basically already a design decision that has been made for a lot of reasons and can't be changed by me. What I was wondering is what is the bet way to populate the MS-Access DB. The DB will only contain 6 tables which will have a very small amount of data in them. I have tried to use ADO.Net to read the data from MS-SQL into a DataTable and then save it into the MS-Access DB using again using a DataTable populated from the first, but I can't seem to ge...

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

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

Importing MS Access data into MS Word
In the thread "combining multiple address fields into 1 address field", Stuart (?) helped solve that problem. I then tried to set up a mail merge in Word to check how I could use and merge the data. I found the database tool bar and opened the appropriate database - but... I could not find a way to open a particular table or query in order to insert the fields from the query into Word. The procedure I followed was this: - open a blank word .doc - from the database tool bar selected "insert database" - a window opened, headed 'Database' and divided into 3 section: at the top a section titled "data source", a middle section titled "data options", a bottom section titled "insert the data into the document" - "under "data source" I clicked "get data" - a window headed 'Select data source' opened - I navigated to the relevant database (mobmem07.mdb) and selected it. - this added the database location to the "data source" part of the 'database' window - I then looked at the "query options" section I found I could not select a table or query from within the database I had opened. Word had merely defaulted to opening one of the two tables in it. The only querying I could do was on the table it had loaded from the functions Word provided. I could not find a way to open any other table let alone a query. Moreover, I couldn't see a wa...

Inserting value from MS Word into MS Access
If there are any enlightend souls out there that can help me, I would greatly appreciate it. I would like to use a macro to take a value and add it to an access database. I am having a problem with the very first line of code, Dim myDatabase as Database, the system does not recognize the Database object. PLEASE if there is anyone out there that has a piece of code that sends info from a word file into a access database... send it to me. I will Love you forever. Evan wrote: > If there are any enlightend souls out there that can help me, I would > greatly appreciate it. I would like to use a macro to take a value and > add it to an access database. > > I am having a problem with the very first line of code, > Dim myDatabase as Database, the system does not recognize the Database > object. Add a reference to DAO or use an Object variable and late bind it (set db=CreateObject("DAO.Database"), although the first way will give you intellisense. -- \\\\\\ \\ \\ Windows is searching \ \ For your sig. \ \ Please Wait. \__\ mmm, I am sorry - I am really new at this... I have added the ADO reference, and I want to execute this SQL - "insert into documents (title) values ('testTitle')" How do I write the actual code, I had it before but then I deleted the wrong file... now I am back at square one Evan wrote: > mmm, I am sorry - I am really new at this... I have added the ADO > referenc...

Web resources about - Possible to keep MS Access interface and migrate the MS Access to MS SQL Server? - comp.databases.ms-sqlserver

Interface - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

PayPal for iOS updated w/ completely new iPhone 6-optimized interface, much more
At long last PayPal has updated its iOS app with a completely redesigned interface that optimizes the experience for iPhone 6/iPhone 6s and 6 ...

Waze 4.0 unveiled with revamped user interface
... post, Waze has uploaded a video straight to YouTube to tell us what’s new. Basically, we are looking at a complete refresh to the user interface, ...

European Electric Bus Makers Will Work Toward Open Charging Interface
... common charging infrastructure in this case refers to that provided [&hellip European Electric Bus Makers Will Work Toward Open Charging Interface ...

Industrial IoT still needs humans and user interfaces
IoT is getting more automated, but not everything can be done by machines. An engineer who's been working with a system for decades has insights ...

Software brings your PC's entire interface to VR
Virtual reality faces numerous challenges, but one of the biggest is having to take your headset off whenever you need to run a conventional ...

LibreOffice 5.1 Offers Reorganized User Interface for Its Apps
LibreOffice 5.1, the latest iteration of the open-source office suite, adds new user interface elements. We examine highlights of the release. ...

AMD is supposedly planning a 32-core CPU with an eight-channel DDR4 interface
AMD may be planning a new 32-core server CPU once Zen launches but whether that's a good thing or not depends on whether its an Intel bunker ...

Hints Of Apple's Futuristic iPhone Interface Revealed
The US Patent Office has released a batch of patents assigned to Apple, and one of them details a new way of identifying yourself and allowing ...

Where is that sound coming from? Signal Interface’s acoustic imager will show you
... industrial site pinpoints sound coming from a passing truck as well as from equipment on the other side of a buildling. (Credit: Signal Interface ...

Resources last updated: 3/22/2016 5:06:25 AM