f



Convert from SQL Server to MS Access (yes that is not a typo)

I have a SQL Server database that is connected to a client server
program via ADO.  I want this same program to be able to use JET as
the database back end (to provide a light alternative that doesn't
require a full install of MSDE or SQL Server.) I know that I can't
necessarily get a fully automated conversion, but I was wondering what
advice group members would have to make this conversion as painless as
possible (not to mention keeping the two in sync.) Perhaps there are
some tools out there that will break the back of the work? I haven't
used JET since 3.5, but I understand that 4.0 has support for stored
procedures. It is possible to convert SQL Server stored procedures to
JET stored procedures?


Thanks for your help.

0
nde_plume (5)
3/2/2007 4:10:24 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

1 Replies
545 Views

Similar Articles

[PageSpeed] 51

In Access, Views and Procedures are called Queries.

You can create queries to some stored procedures (e.g. parameter queries, 
action queries), but JET does not support everything you can do in stored 
procedures. Others will need to be reworked to use different functionality, 
or split into separate queries.

If you use the ADOX library, you can list the Procedures separately from the 
Views in Access code.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<nde_plume@ziplip.com> wrote in message
news:1172808624.788042.13400@30g2000cwc.googlegroups.com...
>I have a SQL Server database that is connected to a client server
> program via ADO.  I want this same program to be able to use JET as
> the database back end (to provide a light alternative that doesn't
> require a full install of MSDE or SQL Server.) I know that I can't
> necessarily get a fully automated conversion, but I was wondering what
> advice group members would have to make this conversion as painless as
> possible (not to mention keeping the two in sync.) Perhaps there are
> some tools out there that will break the back of the work? I haven't
> used JET since 3.5, but I understand that 4.0 has support for stored
> procedures. It is possible to convert SQL Server stored procedures to
> JET stored procedures?
>
> Thanks for your help. 

0
AllenBrowne (3681)
3/2/2007 4:50:13 AM
Reply:

Similar Artilces:

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

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

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

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

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

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

Converting SQL Server Stored Procedures to MS-Access
Hi, How can I export SQL Server Stored Procedures to MS-Access? Cheers H sollento wrote: > Hi, > How can I export SQL Server Stored Procedures to MS-Access? > > Cheers > > H Open the SQL Server SPROC in Query Analyzer, copy the SQL itself, open a new query in Access, paste in the SQL, making sure that there are no "gotchas" such as SQL Server user-defined functions etc. I know of no way to automate this process, though there may be one. Edward -- The reading group's reading group: http://www.bookgroup.org.uk ...

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

Converting MS Access Queries to TSQL in SQL Server
I am attempting to upsize from access to SQL Server. I'm trying to convert my queries to SQL Server views; however, I'm having a lot of difficulty with the syntax differences. For instance, a query with select mid(tblTable.FieldName,1,10)should look something like select substring(tblTable.FieldName,1,10). This is really the simplest of examples. I've tried using the SQL Server Import tool, but it takes the queries and changes them into actual tables in SQL Server. I've attempted to use the upsizing wizard, but I get an OVERFLOW error. Does anyone know of a tool that will co...

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

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

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

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

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

Re: <Another Pluh>Re: Accessing MS SQL server or ORACLE database from the HP3K
From: "PeteVickers" <pete@GUI-INNOVATIONS.COM> > Hi, > forgive the 2nd plug, but we have just uploaded the NewsWire review to the > web site, or use the direct link > http://www.gui-innovations.com/NewsWire0209.pdf > "Paul H. Christidis" <phchristidis@raytheon.com> wrote in message > > Is there a way for an IMAGE/VPLUS based application to connect, extract, > > and present to the user (based on his input) data residing on an MS SQL > > server or an ORACLE database on a different host? <plug> MPE/iX Enterpr...

MATLAB command to access MS Access database
Hi! Is there a MATLAB command which gives access to a .mdb MS Access database or do I need a special driver? Thanks, Andree Hi Andree Ellert, you can use activeXcontrol to interact with MS-Access database. access = actxserver('Access.Application'); returns the handle for Access. set(access, 'Visible', 1); will show the opened Access window. use get(access) and set(access) commands to find the methods available to call. -Vadivelu M =========== http://www.mathworks.com/access/helpdesk/help/toolbox/database/datatool.shtml "Andree Ellert" <ellert@gmx.net/////\\\\\> wrote in message news:eed9874.-1@webx.raydaftYaTP... > Hi! > > Is there a MATLAB command which gives access to a .mdb MS Access > database or do I need a special driver? > > Thanks, > > Andree Andree Ellert wrote: > > > Hi! > > Is there a MATLAB command which gives access to a .mdb MS Access > database or do I need a special driver? > > Thanks, > > Andree <http://www.mathworks.com/matlabcentral/fileexchange/loadFile.do?objectId=4045&objectType=file> -Hardik ...

request for explanation on access an database in ms access
This is the code that I found on the internet for accessing an ms access database: import java.sql.*; class Test { public static void main(String[] args) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // set this to a MS Access DB you have on your machine String filename = "d:/java/mdbTEST.mdb"; String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="; database+= filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end // now we can get the connection from the DriverManager Connection con = DriverManager.getConnection( database ,"",""); } catch (Exception e) { System.out.println("Error: " + e); } } } What I don't understand is the declaration of database. What I did was putting the database in the same dir as the sourcecode and use "jdbc:odbc:CafeJolt.mdb". But this doesn't work. And the above code does, why is that??? Actually it's not Java question. It's ODBC question. You have to have ODBC connection. Depending of Windows it's usually something like this (for my Windows 2000): Start->Sttings->Control Panel->Administartive Tools->Data Sources(ODBC)-> User DSN -> [Add]-> Driver to Microsoft Access(*.mdb) === And here, finally you can choose file name. ...

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

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

Converting MS Access SQL Query With INNER JOIN To Oracle SQL
http://www.progneer.com/wp/information_more_public.aspx?search_fd0=132496 On Sep 10, 8:17=A0am, "takv...@gmail.com" <takv...@gmail.com> wrote: > http://www.progneer.com/wp/information_more_public.aspx?search_fd0=3D13..= .. > "Moreover, in MS Access using dates is very simple and straight-forward, = all you have to do is to surround the date by # (hash symbol) but in Oracle= SQL to use the date in the WHERE clause you have to use the TO_DATE functi= on." WTF??? No, I don't. select count(*) from sales_order_headers where order_date > '01- jan-201...

Converting MS Access SQL Top N Query To Equivalent Oracle SQL
http://www.progneer.com/wp/information_more_public.aspx?search_fd0=132497 On Sep 10, 8:46=A0am, "takv...@gmail.com" <takv...@gmail.com> wrote: > http://www.progneer.com/wp/information_more_public.aspx?search_fd0=3D13..= .. The FAQ has a much better answer. Sorry if analytics are too much rocket science for you. http://asktom.oracle.com/pls/apex/f?p=3D100:11:0::= ::P11_QUESTION_ID:1137689100346245972 and http://asktom.oracle.com/pls/asktom/f?p=3D100:11:0::::P11_QUESTION_ID:9= 48366252775 are even better answers, they show how to do things with rownum and what o...

Converting Format Function In MS Access SQL Query To Equivalent Oracle SQL
http://www.progneer.com/wp/information_more_public.aspx?search_fd0=132498 Stop that spamming! takveen@gmail.com wrote on 10.09.2010 18:50: > http://www.progneer.com/wp/information_more_public.aspx?search_fd0=132498 ...

Web resources about - Convert from SQL Server to MS Access (yes that is not a typo) - comp.databases.ms-access

File:Convert to SVG and move to Commons.svg - Wikipedia, the free encyclopedia
As a courtesy (but not a requirement), please e-mail me or leave a note on my talk page if you use this image outside of Wikipedia. Thanks! As ...

Facebook Co-Founder Dustin Moskovitz Sells 450K Shares, Converts 7M More
The great Facebook stock sale frenzy continues on with a report that Co-Founder Dustin Moskovitz unloaded 450,000 shares in the social network ...

Facebook Announces “App2User” Liquidity Program for Merchants to Convert Rewards Points into Credits
... for Credits, just announced a new Credits program called “App2User,” designed to enable merchants and loyalty program operators to convert their ...

How to Convert Leads Into Buying Customers - The 9 Step Sales Process EVERYONE Should Use
... School of Business in Toronto on the topics of Sales and Entrepreneurship. I shared with them the sales process that I find How to Convert Leads ...

Bitcoin : Should I convert my savings to Bitcoin?
Answer (1 of 7): You should think of this question as "Should I invest all my savings in Bitcoin?", and not "Should I have my savings denominated ...

Convertizo 2 - Convert Units and Currency in Style on the App Store on iTunes
Get Convertizo 2 - Convert Units and Currency in Style on the App Store. See screenshots and ratings, and read customer reviews.

Coca-Cola made packaging that converts into a free Google Cardboard-style VR viewer [Video]
... if the above experiment turns into reality. The project envisions packaging for a 12-pack of Cokes and other products that is easily converted ...

Coca-Cola made packaging that converts into a free Google Cardboard-style VR viewer for iPhone
... if the above experiment turns into reality. The project envisions packaging for a 12-pack of Cokes and other products that is easily converted ...

The Galaxy S7 Edge may have just made me a Samsung convert
Historically, I've had a begrudging respect for Samsung flagships. I can objectively acknowledge the company makes very good, extremely popular ...

This modular robot tank converts into a tracked hoverboard
Filed under: Etc. , Videos , Autonomous , Military , Off-Road The unmanned vehicle is equal parts hoverboard and tank.

Resources last updated: 3/20/2016 11:59:36 PM