f



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 convert the MS Access query syntax to
SQL Server?


0
10/13/2003 4:25:30 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

2 Replies
708 Views

Similar Articles

[PageSpeed] 51

OVERFLOW errors are almost always caused by DATE fields that have bad data,
say, a date
like 2/5/1099. That date is legal in Access, but not in SQL Server.

Make sure that all of your dates are valid SQL Server dates before upsizing.

From ther SQL Server 7 Books Online:

datetime:
Date and time data from January 1, 1753, to December 31, 9999, with an accuracy
of three-hundredths of a second, or 3.33 milliseconds. 

smalldatetime:
Date and time data from January 1, 1900, through June 6, 2079, with an accuracy
of one minute. 

To play it safe, you should make sure all of your dates are >= 1/1/1900. If you
absolutely need dates prior to that, then make sure they're all >= 1/1/1753,
and pray the upsize wizard uses datetime. If you need dates prior to that,
(maybe your application is tracking composer birth/death dates), then you have
to use a text field.

As far as converting your queries to views, SQL Server doesn't have near the
rich function library that Access has available to it. The MS Access dev team
has perfomed a small miracle by making Access SQL recognize VBA functions!

There's no shortcut for this...you nust convert them manually if they use VBA
functions. 
0
dcmfan (81)
10/13/2003 5:42:23 AM
"Dana Shields" <danashields@softhome.net> wrote:

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

FWIW where possible move the functionality out of the query.  Accesses built in
functions makes life so much easier we're quite spoiled.   So in this example can you
move the mid function to the form, report or VBA code?

Tony
--
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
0
ttoews (2789)
10/18/2003 12:37:38 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...

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

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

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

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

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

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

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

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

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

ms access to sql server query help!
Hi All, I'm desperately trying to use an old access query and translate it to be used in SQL enterprise manager. The dataset i'm trying to update is too large for access (28 million rows). IIf([TableName]![FIELDNAME] Like "??0*",Left([TableName]![FIELDNAME], 2) & Right([TableName]![FIELDNAME],4),IIf([TableName]![FIELDNAME] Like "?0*",Left([TableName]![FIELDNAME],1) & Right([TableName]! [FIELDNAME], 4),[TableName]![FIELDNAME])) This works to remove padding zeros in the middle of a postcode field. eg SR04 PQJ would be SR4 PQJ or S03 4PY would be S3 4PY. It does this by finding all postcodes that begin with either ??0 or ? 0 and then recompiles them using concatenation. This works fine in access but i can't translate it to be read in SQL server. I'm aware of the differences in wildcard chars and the CONCAT function but cannot seem to get it right. Could someone please help!! In SQL Server: 1. There is no such function as Iif, you need to use the CASE function. 2. The string delimiter is a single quote mark, not a double quote mark. 3. "*" is not a wildcard character, you need to use the "%" character. 4. The "!" character will not be recognised, use a full stop character. 5. The "&" operator does not exist, the nearest equivalent is the "+" operator. SQL Server 2000 Books Online can be downloaded for free: http://www.microsoft.com/downloads/details.aspx?Fa...

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

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

Trouble when submitting sql-query to MS Access Database
Hi, I have some problems when trying to submit a simple insertion statement to a MS Access Database. The reading is fine, but when I want to execute an insertion statement using the following code: public void SQLCommand(String sql) throws SQLException { connection = DriverManager.getConnection(database); Statement stmt = connection.createStatement(); stmt.executeUpdate(sql); connection.close(); } gives the following error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source) at org.strumpflohner.CodeNotebook.Data.DBManager.SQLCommand(DBManager.java:45) at org.strumpflohner.CodeNotebook.Data.TestClass.main(TestClass.java:15) Could someone help me? kito Suggestion 1: The error is caused because you're referring to a table that does not exist. Try to run the query in a seperate query editor and verify that it does work. Also check that the string you pass as argument is correct, e.g. not concatenation that has destroyed the intended string. Suggestion 2: You probably need to add quotes in the executeQuery. Whenever you pass a string to a database try to inclose the statement with...

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

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

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

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

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

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

Why Same query results in two different # in SQL Server vs MS Access
Hello, I have one simple query joining two tables with left outer join on 3 fields and using MIN on two fields. These two tables have lot of data about 3 mil in total. I am trying to migrate db from MS Access to SQL 2005. I get about 689000 rows in SQL Server, vs 863000 rows in MS Access. SELECT T1.[MON], T1.[ANUM], T2.[ANUM], MIN ( T1.[OCD]), MIN(T1. [STATE]), T1.COUNT FROM T1 INNER JOIN T2 ON T1.MON = T2.MON AND T1.[OCD] = T2.[OCD] AND T1.[STATE] = T2.[STATE] WHERE T1.[REASON] <> 'SOMETHING' AND T2.[REASON] <> 'SOMETHING' GROUP BY T1.[MON], T1.[ANUM], T2.[ANUM], T1.COUNT HAVING T1.[MON] <> '-' AND T1.[ANUM] <> '-' I have about 30 queries to migrate and I am sort of stuck. Does any one have any idea ? JB On Wed, 20 Feb 2008 11:31:46 -0800 (PST), dsdevonsomer@gmail.com wrote: >Hello, >I have one simple query joining two tables with left outer join on 3 >fields and using MIN on two fields. These two tables have lot of data >about 3 mil in total. I am trying to migrate db from MS Access to SQL >2005. I get about 689000 rows in SQL Server, vs 863000 rows in MS >Access. > >SELECT T1.[MON], T1.[ANUM], T2.[ANUM], MIN ( T1.[OCD]), MIN(T1. >[STATE]), T1.COUNT > >FROM T1 INNER JOIN T2 ON T1.MON = T2.MON AND T1.[OCD] = T2.[OCD] AND >T1.[STATE] = T2.[STATE] > >WHERE T1.[REASON] <> 'SOMETHING' AND T2.[REASON] <> 'SOMETHING' > >GROUP BY T1.[MON], T...

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