f



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, however it keeps giving me
"Invalid use of null" or "The statement is not written properly or is
too complex.." errors.
Here is the Access statement I am trying to run:

SELECT     IIF(cstr(t2.QType) = '3', t1.Note, IIF(cstr(t2.QType) =
'2', IIF( cstr(t1.ANumber) = '1','Yes','No'), IIF( cstr(t2.Qnumber) +
'.' + cstr(t1.ANumber) = t2.SubQ, 'Selected', 'Not Selected')))
		 AS Client_Response
FROM (SELECT * FROM ClientAnswer
WHERE (cstr(OrganizationID) = '491') AND (InstrumentId = 'CM005')) AS
t1
RIGHT JOIN
(SELECT Questions.InstrumentId, Questions.Qnumber, Questions.Question,
Questions.QType, iif(TEMPT.A = NULL, QNumber, TEMPT.A) AS SubQ,
 iif(TEMPT.Answer = NULL, Questions.Question, TEMPT.Answer) as Answer	
FROM Questions 
LEFT OUTER JOIN 
(SELECT QNumber AS Q, Answer, cstr(QNumber) + '.' + cstr(ANumber) AS A
FROM Answers WHERE (InstrumentId = 'CM005')) TEMPT ON
Questions.Qnumber = TEMPT.Q
WHERE (Questions.InstrumentId = 'CM005')) 
AS t2 ON t1.QNumber = iif(t2.QType <> '1', t2.Subq, t2.QNumber)
ORDER BY subq

I HATE ACCESS!!!
Please help...
0
4/26/2004 9:03:50 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

1 Replies
785 Views

Similar Articles

[PageSpeed] 3

"Stefan V." <stefanvantchev@hotmail.com> wrote in message
news:1c08074d.0404261303.3335add2@posting.google.com...
> 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
>
>


1. Format the original statement with line breaks and indentations, (easy to
do in Query Analyzer). It makes the code much easier to read - for us as
well as you.
2. Use IIf or the Switch statement, (depending on number of conditaions)
instead of CASE .. WHEN .. ELSE
3. Use CLng to convert text to numeric datatype and CStr to go the other way
4. CASE .. WHEN IS NULL THEN can be replaced by COALESCE in sql server and
Nz() in Access (Jet)
5. Use RIGHT OUTER join instead of RIGHT JOIN (I'm not 100% sure but IU
don't think you can use RIGHT JOIN in Access)


And some general notes
6. It looks like the first CASE statement can be simplified - case gives you
a choice between 1, 2, 3 .. n options - you don't have to embed multiple
case statetements as you would do with IIf.
7. Why do you alias some tables and not others?
8. SELECT t2.* is not good sql programming - it's better to explicitly
declare all your columns.
..










0
assaynet (289)
4/27/2004 12:21:47 AM
Reply:

Similar Artilces:

Update SQL 2000 Query (converting an Old Access 2k query to SQL)
Hello, I have the following query in Access 2000 that I need to convert to SQL 2000: UPDATE tblShoes, tblBoxes SET tblShoes.Laces1 = Null WHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND ((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B")) WITH OWNERACCESS OPTION; The ShoesID in the tblShoes table is an autonumber, however the records in the tblBoxes have the ShoesID converted to text. This query runs ok in Access, but when I try to run it in the SQL Server 2000 Query Analizer I get errors because of the comma in the "UPDATE tblShoes, tblBoxes" part. I on...

Converting MS query to SQL Server 2000
I am struggling rewriting my query from MS Access' IIF, Then to SQL Servers TSQL language. I am hoping some one can give me some guidance. I believe I have the first portion of the query correct but do believe this requires a "NESTED" argument. This is where I am lost. My Original MS ACCESS Query reads-- SELECT DISTINCTROW REGION_TRAFIC.*, IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE], IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4], IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3], IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2], IIf([Carrier]="VENDOR1",[DOMESTIC LD RATES]![INTRA_VENDOR1])))), IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4], IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3], IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2], IIf([Carrier]="VENDOR1",[DOMESTIC LD RATES]![INTER_VENDOR1]))))) AS CPM, [CPM]*[MOU] AS COST INTO INTRALATA_LD FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA])) ORDER BY REGION_TRAFIC.[TERM LATA] DESC; I have tried to re-write this in SQL SERVER as -- SELET DISTINCT REGION TRAFIC.*, CASE WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD RATES].INTRA_VENDOR4 ...

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

Using both IS NULL and IS NOT NULL in Access 2000 Pass Through Query (for SQL Server 2000)
Hello, I'm in the process of changing our 'normal' Access 2000 update queries to Update Pass Through Queries. We have a SQL server 2000 database and we're using an Access 2000 database as our front end. In the criteria of one of our update query fields, we use both the isnull(field1) and Not IsNull(field2) then Field3, otherwise, set the updated field as null. Here's my converted Pass Through Query : UPDATE Vsel SET VSel.Cert1 = IIf(IsNull(VSel.Cert1), RenArch.Cert1), VSel.Cert2 = IIf(IsNull(VSel.Cert2), RenArch.Cert2), VSel.Cert3 = IIf(IsNull(VSel.Cert3), RenArch.Cert3), VSel.Cert4 = IIf(IsNull(VSel.Cert4), RenArch.Cert4), VSel.Cert1ExpDt = IIf(IsNull(BDACmpltDt) AND IS NOT NULL (RenArch.Cert1), RenArch.CDDt), VSel.Cert2ExpDt = IIf(IsNull(BOMSADtIssd) AND IS NOT NULL (RenArch.Cert2), RenArch.CDDt), VSel.Cert3ExpDt = IIf(IsNull(BDPMADtIssd) AND IS NOT NULL (RenArch.Cert3), RenArch.CDDt), VSel.Cert4ExpDt = IIf(IsNull(BOACmpltDt) AND IS NOT NULL (RenArch.Cert4), RenArch.CDDt), VSel.BDACmpltDt = IIf(IsNull(BDACmpltDt), RenArch.Cert1DOC), VSel.BOACmpltDt = IIf(IsNull(BOACmpltDt), RenArch.Cert4DOC), VSel.BDPMADtIssd = IIf(IsNull(BDPMADtIssd), RenArch.Cert3DOC), VSel.BOMSADtIssd = IIf(IsNull(BOMSADtIssd), RenArch.Cert2DOC) FROM VSel INNER JOIN RenArch ON VSel.SSN = RenArch.SSN This is the original criteria in the access update query for line #6 (when I start to use both is null and is not null) IIf(IsNull([BDACmpltDt]) And Not IsNull([RenArch].[Cert1]),[Re...

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

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

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

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

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

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

MS Access 2000 Crosstab Queries
Can someone tell me if this is possible? Crosstab for a registration table, i.e., wa or ca id wy ....... Brand Product 1 1 (count of product) The output I need is Brand Product wa, ca Any help will be most appreciated....thank you in advance. bgill@wecon.com ...

How to run a query in Ms-Access 2000
I've written a query in a notepad. I want to test whether it works or not. I don't find any option to run user written queries in in Ms-Access 2000. When i ckick on 'Queries' tab, it gives option to 'create query in design view' and 'create query by using wizard'. There is no option for the user to write his queries on his own instead of building it using those utilities. If anyone know how to write our own queries and test them in access-2000, pls inform. It will be a great help. Thank you. ralamo wrote: > I've written a query in a notepad. I want to test whether it works or > not. I don't find any option to run user written queries in in > Ms-Access 2000. When i ckick on 'Queries' tab, it gives option to > 'create query in design view' and 'create query by using wizard'. There > is no option for the user to write his queries on his own instead of > building it using those utilities. If anyone know how to write our own > queries and test them in access-2000, pls inform. It will be a great > help. Thank you. Copy your query from Notepad. Open up the query design grid. Click on view. Click on SQL view and paste your query there. You will of course need the table and field names to be in the DB Thank you so much. Yes .... i'm ableto run the query but some errors. But it's ok. Now i know how to run user written queries. Thanks again. you could, of course, create a new query in...

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

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

Accessing a passworded MS Access 2000 database in Crystal Reports
Can anybody give me some code that I can put in to my VB6 app to open a passworded Access database in the Crystal Reports 8.5 RDC? I see so many examples - none of which seem to work for me. I am using a native connection in Crystal ( pdbdao.dll). My current code is : Dim Report As New crPrintPreview Dim crTable As CRAXDRT.DatabaseTable Private Sub Form_Load() Set crTable = Report.Database.Tables.Item(1) varpath = CurDir crTable.Location = varpath & "\OrderManager.mdb" CRViewer1.ReportSource = Report CRViewer1.ViewReport End Sub All I receive is the message "Error opening file xxxxxxx etc" and then "Open Database session failed" Appreciate any help! Peter Tyler ...

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

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

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

Accessing a passworded MS Access 2000 database in Crystal Reports #2
Can anybody give me some code that I can put in to my VB6 app to open a passworded Access database in the Crystal Reports 8.5 RDC? I see so many examples - none of which seem to work for me. I am using a native connection in Crystal ( pdbdao.dll). My current code is : Dim Report As New crPrintPreview Dim crTable As CRAXDRT.DatabaseTable Private Sub Form_Load() Set crTable = Report.Database.Tables.Item(1) varpath = CurDir crTable.Location = varpath & "\OrderManager.mdb" CRViewer1.ReportSource = Report CRViewer1.ViewReport End Sub All I receive is the message "Error opening file xxxxxxx etc" and then "Open Database session failed" Appreciate any help! Peter Tyler ...

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

Web resources about - Converting SQL Server 2000 query to MS Access 2000 query - comp.databases.ms-access

Facebook Begins Converting Users To HTTPS
Are you willing to sacrifice a little bit of speed for a lot more safety? Facebook is asking that very question with its announcement that it ...

Facebook No Longer Converting Groups Into Pages
Back when Facebook first launched Facebook Pages, many businesses and brands who had built up substantial audiences in their Facebook Groups ...

Vert - simply converting for iPhone, iPad, and iPod touch on the iTunes App Store
Get Vert - simply converting on the App Store. See screenshots and ratings, and read customer reviews.

Converting SIM Card to Micro SIM Card - Flickr - Photo Sharing!
Place new Micro SIM into the iPhone SIM card tray

Ayaan Hirsi Ali on Converting Muslims to Christianity - YouTube
Complete video at: http://fora.tv/2010/07/29/Nomad_From_Islam_to_America_with_Ayaan_Hirsi_Ali Ayaan Hirsi Ali explains her support of missionary ...

Converting dry air to water: solution to Broken Hill's water crisis gains support
A one-man crusade by a Broken Hill resident to solve the historic town's water crisis by introducing air to water converters is gaining support. ...

Click go fears of converting print files
Is there a way to convert a print queue item to a .RTF or .PDF file? I like to save or email them. - The Sydney Morning Herald

Sudanese woman ordered to hang under sharia law for converting to Christianity gives birth
Khartoum, Sudan: A Christian Sudanese woman sentenced to hang for apostasy has given birth in jail, a Western diplomat said on Tuesday.

Imams warn against radicalism to Aboriginal inmates converting to Islam
The prison system has enlisted the help of ASIO to crack down on radicalisation behind bars amid revelations that Aboriginals are converting ...

Converting the world's companies one by one - The Science Show - ABC Radio National (Australian Broadcasting ...
Image: Trucks carrying logs make their way up a road in Jambi, Indonesia. A vast area of the Sumatran forest, and orangutan habitat, is being ...

Resources last updated: 3/21/2016 5:26:01 PM