Convert Access Queries To SQL

Oct 15, 2007

I have an Access database that used to produce a mass of Performance Indicators from Access tables. The data is now held on SQL Server and I run the Access queries from the SQL tables. I wouldlike to move all the queries over to SQL but not sure if I can do that. Here's an example of one of the queries (the SQL view)

SELECT tblCalls.*
FROM tblCalls
WHERE (((tblCalls.Call_date)>=[Forms]![ReportParams]![SDate] And (tblCalls.Call_date)<=[Forms]![ReportParams]![EDate]) AND ((tblCalls.NotAccepted)=False) AND ((tblCalls.Completed_time) Is Not Null) AND ((tblCalls.Category)="fly tipping"));


SELECT Month([Call_date]) AS Mnth, DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0) AS Qtr, Sum(Work_Days([Call_date],[Completed_time])) AS RespTime, Sum(1) AS Count, Sum(Work_Days([Call_date],[Completed_time]))/[Count] AS AvgTime
FROM qryFlyTippingStatsSummary1
GROUP BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0)
ORDER BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0);

View 2 Replies


ADVERTISEMENT

Access Queries Migration

Mar 21, 2000

Hello all,
Does anyone know of a utility to migrate Access 97 Queries to SQL Server 7?
I've over 400 of them and I am trying not to have to do them by hand.

Also I tried to use to migration wizard from MS instead of the DTS and all I got was Error after error. 'Invalid Date/Time', 'Error with YES/NO field' and 'Timed out' were the most common. In the end only 4 tables out of 89 were migrated.

Any suggestions would be greatly Appreciated.

-Ray

View 2 Replies View Related

Access To Server Queries

Mar 13, 2013

I am trying to get the same results in SQL server with a query as it works in access

There are two tables:

TITLES
Fields in query: TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel

Fields in Table:

[TitleID]
,[TitleNum]
,[TitleState]
,[VehYR]
,[VehMake]
,[VehModel]
,[NewORUsed]

[code]...

How this can be ONE SQL Query?In query designer on access theleft table is TITLES_IN_OFFICES_qry and the table on the right isTITLOC.I have never used a query as a table before in a select .

View 4 Replies View Related

Using SQL And Writing Queries In Access

Aug 6, 2005

Hello~

I am wondering if anyone can recommend a basic, "How To" article, column or book for someone who has never worked with Access but is familiar with DB programming.

I need to know how to submit queries, and print reports.

Any help is appreciated.

Thank you!

Shelli

View 2 Replies View Related

Cannot Access Any Tables In Queries

Nov 7, 2007



I am using SQL2005 (Installed on VISTA). It was running smoothly until today....for some reason I cannot access any tables in queries that I try to run in Management Studio. I can open the tables and see the information, but all queries I try to run result in the following error:


Msg 208, Level 16, State 1, Line 1

Invalid object name 'DBO.BEYLEVEL16'.


I tried to run simple select queries on other tables but I get the same error message. Do not understand, eveything was working fine yesterday.

Any suggestions greatly appreciated.
Thanks

View 9 Replies View Related

SQL Queries For An Access Database

Oct 6, 2006

Hi,

I'm working on an electronic register which people use to sign in and out of a building. It uses 3 tables, one to store personal information (Name & Visitor Number), one to store what time they come in, and one to store what time they leave. Visitor Number is the primary key in the Visitor_Info table, and is used as a foreign key in the other 2 tables.

I've been asked to put in an 'emergency print' function where, when you click the print button, it compares the data in the time in database and the time out database and prints a list of people still in the building.

So, what I'm trying to do is create a SQL query that will give me this information. Having never used SQL before, it's a little tricky. I've spent the last few days looking at tutorials on the net and learning about queries and this is what I've come up with:

Select Time_In_Table.Visitor_Number
Not Union
Select Time_Out_Table.Visitor_Number

If I'm right (and I'm probably not), this will give me the people who are in the time in table, but not in the time out table.

Can someone please help me by letting me know what I'm doing wrong in the statement above, and also, advise me as to where it goes since none of the tutorials I've found cover where to put the statement in my programming? At a guess, I'd say it goes in the print button click function.

View 7 Replies View Related

Moving Access 97 Queries Over To SQL Server 7

Apr 6, 2000

Does anyone out there know of any utility that can Migrate Queries to SQL Server 7. I have over 400 of them and Dread doing them all by Hand.

Thanks,
-Ray

View 2 Replies View Related

Migrating Access Queries To SQL Server 7.0

Nov 2, 2000

I have a query in Access that has an iif statement in the select system like so:

SELECT [01_qryCommonCost_01].*, tblPercent_Afe.AfeDescription,
tblPercent_Vendor.[Vendor%], tblPercent_Afe.[Afe%], IIf([manual%] Is Not
Null,[Manual%],IIf([PropType]="NonMkt",0,IIf([Vendor%] Is Not
Null,[Vendor%],IIf([Afe%] Is Not Null And [GlType]<>"OpExp",[Afe%],[Gl%]))))
AS [%], 0+round2([Amount]*[%]) AS Allocated,
0+round2([Allocated]*[Salvage%]) AS Salvage
FROM (01_qryCommonCost_01 LEFT JOIN tblPercent_Vendor ON
([01_qryCommonCost_01].GlType = tblPercent_Vendor.GlTyp) AND
([01_qryCommonCost_01].VendorName = tblPercent_Vendor.VendorName)) LEFT JOIN
tblPercent_Afe ON ([01_qryCommonCost_01].AfeNo = tblPercent_Afe.AfeNo) AND
([01_qryCommonCost_01].Group = tblPercent_Afe.Group)

But I can't figure out how to nest the iif statement in T-SQL. Any hints?

View 2 Replies View Related

Move Data From Access To Sql Server 7.0 Using Sql Queries

Jan 22, 2008

Hi All,
I hope I am at the right place to post this question:
How can I move the tables with its data from Access to SQL Server 7.0 using SQL queries. Later I might put everything in the store procedure and have a third party running the store-procedure to do it by itself.
Please advice what should I do first? Do I create new tables in the s SQL Server 7 or can I move the data and create the tables at the same time?..Thank you.

View 1 Replies View Related

Can I Write Multiple 'Select' Queries In Access? Please Help!

Jul 22, 2004

I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)

I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.


Code:


SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)

SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;

drop table dupContactKeys



This of course doesn't work. Please help, as I am going slightly mad!

View 5 Replies View Related

Parameterized Queries - Works In Access But Not SQLS2k?

Feb 3, 2004

I have an application where users can enter data into any (or all) of 6 search fields,
to produce a filtered query.

This works fine using my Access version(see code below),
but as SQLS2k cannot use "IIF", I tried to replace these bits with
"CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields
as these cannot be "wild-carded" in the same way as Access allows.

Can anyone suggest a way forward that does not involve coding all the
possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?

Hoping you can help
Alex








PARAMETERS
CurrentType Text,
CurrentCategoryID Long,
CurrentProductID Long,
CurrentClientID Long,
CurrentContractID Long,
FromDate DateTime,
ToDate DateTime;

SELECT
tAudit.AuditID,
tAudit.ActionType,
tAudit.ClientID,
tClients.ContactCompanyName,
tAudit.ContractID,
tContracts.ClientRef,
tAudit.ProductID,
tProducts.ProductName,
tAudit.CategoryID,
tCategories.CategoryName,
tAudit.Acknowledged,
tAudit.ValueAmount,
tAudit.DateStamp

FROM (((tAudit
LEFT JOIN tCategories
ON tAudit.CategoryID = tCategories.CategoryID)
LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID)
LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID)
LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID

WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType]))
AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID]))
AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID]))
AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID]))
AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID]))
AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));

View 2 Replies View Related

SQL 2012 :: Converting Access Queries To Views

May 13, 2014

Is there an easy way to convert Access Queries to SQL Views without doing it manually?I have used the Databse tool to migrate tables, but cannot see to find something similiar for queries.

View 3 Replies View Related

Parameterized Pass-through Queries From Access Front-end?

Jul 23, 2005

Is there any easy way to pass (dynamically) parameters to pass-throughqueries,when working with MS Access as front-end for SQL Server ?Thanks.

View 1 Replies View Related

MS Access Adp Queries And SQL Serv 2005 Express

Sep 16, 2006

I have upsized some Access 2002 tables to Sql Server 2005 express. I put together a query and when I try to execute it, it tells me the MS Access is an earlier version prior to SQL server 2005 and it will not save the queries. When I try to run them anyway, I get the repeated message and the query never executes. I'm taking a T-SQL class and this is how the instructor. Is they a fix for SQL Server or Access to get around this or do I have to revert back to MDSE 2000?? Can MDSE 2000 and SQL Server be loaded onto the same machine (laptop)? Help!!!!! I am very new at this...

View 5 Replies View Related

Converting Stored Procedures Back To MS Access Queries

Mar 15, 2007

I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.

We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?

Any advice would be greatly appreciated.

Many thanks
Smilla

View 1 Replies View Related

Convert Access To SQL

Jul 15, 2006

The following is my code for Access... can someone help me convert it to sql:
My Connectionstring is "server=(local);database=Database;trusted_connection=true"
 
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
Sub btnLogin_OnClick(Src As Object, E As EventArgs)
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim intUserCount As Integer
Dim strSQL As String
strSQL = "SELECT COUNT(*) FROM tblLoginInfo " _
& "WHERE username='" & Replace(txtUsername.Text, "'", "''") & "' " _
& "AND password='" & Replace(txtPassword.Text, "'", "''") & "';"
myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & Server.MapPath("login.mdb") & ";")
myCommand = New OleDbCommand(strSQL, myConnection)
myConnection.Open()
intUserCount = myCommand.ExecuteScalar()
myConnection.Close()
If intUserCount > 0 Then
lblInvalid.Text = ""
FormsAuthentication.SetAuthCookie(txtUsername.Text, True)
Response.Redirect("login_db-protected.aspx")
Else
lblInvalid.Text = "Sorry... try again..."
End If
End Sub
</script>

View 4 Replies View Related

Convert From Access DB To SQL DB....

Nov 4, 2007

Dear All,
 I would like to convert from Access To SQL DB undervisual Studio.Net 2005...
How can I do it easily, or if there any software to do this automaticlly, please your help..
 Awaiting your valuable reply.
Many thanks in advance for your cooperation and continuous support....

View 6 Replies View Related

DB Convert From Access

Feb 16, 2005

I am currently in the process of writing an application to convert an Access database to SQL. Basically, I have created an odbc link in access and then I transfer the data from the access table to the linked table.

The problem is where I have to transfer the Identity keys. I can transfer all the data but the keys. They will auto increment. I tried using INSERT_IDENTITY tablename ON but it just returns an error mosty of the time. I have gotten it to transfer once or twice by stepping through.

Here is the basic code:

The code that causes everything to error is commented out.


Private Sub CopySQLRecordSet(ByVal stTableName As String, _
Optional ByVal blIdentity As Boolean = True, _
Optional ByVal stSearch As String = "")
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
Dim adoCommand As New ADODB.Command, stCommand As String
Dim adoSQLCommand As New ADODB.Command
Dim errorString As String

On Error GoTo ErrorCopying
PrgPart.Value = PrgPart.Value + 1
If blCancelPressed Then End '???fix later
cat.ActiveConnection = db1 'This doesn't seem to work with our normal settings for spectrumDbase
tbl.ParentCatalog = cat
tbl.Name = "dbo_" & stTableName
tbl.Properties("Temporary Table") = False
'possibly, this line will work for Oracle as well
tbl.Properties("Jet OLEDB:Link Provider String") = "odbc;DSN=
;DATABASE=database;"
tbl.Properties("Jet OLEDB:Remote Table Name") = stTableName
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Table Hidden In Access") = False
tbl.Properties("Jet OLEDB:Cache Link Name/Password") = False
cat.Tables.Append tbl
Suspend 1
adoCommand.CommandType = adCmdText
adoCommand.ActiveConnection = database
adoSQLCommand.CommandType = adCmdText
adoSQLCommand.ActiveConnection = rsDbase

'spectrumDBase.BeginTrans
'stCommand = "BEGIN TRANSACTION " & vbNewLine _
' & "go" & vbNewLine
'If there is an identity field in the table, it must be temporarily disabled
'to insert from an foreign DB.
'If blIdentity Then
' stCommand = stCommand & "SET IDENTITY_INSERT dbo_" & stTableName & " ON " & vbNewLine & "GO" & vbNewLine
'adoCommand.CommandText = stCommand
'adoCommand.Execute
'End If

'insert the records from the source table
stCommand = ""
stCommand = stCommand & "INSERT INTO dbo_" & stTableName _
& " SELECT * FROM " & stTableName & vbNewLine _
& "GO" & vbNewLine

'stCommand = stCommand & "COMMIT TRANSACTION"

adoCommand.CommandText = stCommand
adoCommand.Execute

'clean up
' stCommand = stCommand & vbNewLine & "DROP TABLE dbo_" & stTableName & vbNewLine & "GO"
' adoCommand.CommandText = stCommand
' adoCommand.Execute

'reinsert identity property
' If blIdentity Then stCommand = "SET IDENTITY_INSERT " & stTableName & " OFF" & vbNewLine

'Block statement, going to leave this out for now
'stCommand = stCommand & "go" & vbNewLine _
'& "COMMIT TRANSACTION"

'adoCommand.CommandText = stCommand
'adoCommand.Execute

Set adoCommand = Nothing
Set adoSQLCommand = Nothing
Exit Sub
ErrorCopying:
'need to save this to a string so it doesn't reset when Resume occurs
errorString = Err.Description
' MsgBox "Error copying the [" & stTableName & "] table." & vbNewLine _
' & "Error: " & errorstring
Resume errorCatch
errorCatch:
On Error Resume Next
'set a log
Dim fso As FileSystemObject, fStream As TextStream
Set fso = New FileSystemObject
Set fStream = fso.OpenTextFile(App.Path & "DBTransfer.log", ForAppending, True)
fStream.WriteLine "***Error copying the [" & stTableName & "] table."
fStream.WriteLine " Error: " & errorString
fStream.WriteLine " "
fStream.Close
Set fStream = Nothing
Set fso = Nothing
'clean up
adoCommand.CommandText = "DROP TABLE dbo_" & stTableName
adoCommand.Execute
If blIdentity Then
'reinsert identity property
adoSQLCommand.CommandText = "SET IDENTITY_INSERT " & stTableName & " OFF"
adoSQLCommand.Execute
End If
Set adoCommand = Nothing
Set adoSQLCommand = Nothing
End Sub

View 8 Replies View Related

Convert Access Dba To Ms Sql

Jan 24, 2008

Who convert access dba to ms sql 2000 standard.
I try with access conversion, but some buttons commands and rules donīt work.
The dba contains tabs, forms, relīs, macros, modules and relations with tabs and examination images. Itīs possible convert everything without damage or transform this itens?
The dba contains critical information about identification and personal clinic story about patients, but the access capacity itīs out (2GB) and i need to expand this dba.

I have some urgency to this problem, itīs depends to buy a windows 2008 server and mssql server 2008.

I hope everyone help me.

Thanks,

View 1 Replies View Related

Access 2000 Queries Performing Slower On More Powerful Server

Jun 27, 2001

Hi,

We have recently upgraded our production server from a dual pentium II 400mhz server with 384Mb of RAM to a triple 700mhz Pentium III system with 2gb of RAM . However, since switching over, all of our Access queries on the SQL7 databases are either running extremely slowly or not at all despite the DSN's being set up correctly . Does anyone have any ideas why, and more importantly, how I can resolve this???

Thanks

Pete Burton
(IT Support)
Durham Aged Mineworkers Homes Association

View 2 Replies View Related

How To Convert A Database From MS Access To MS SQL

Feb 9, 2004

hi all,

How to convert a database from MS Access to MS SQL, is there some tool like that?

View 1 Replies View Related

Convert This Access Code To Sql

Jun 24, 2008

Hello does anyone know how to convert this access code to sql. Also is there a program out there that will do it for you.

IIf([Ceridian]![UnionIndicator]="U" And [YearsService]>=3,[HealthBenefits]![medplan.Bi-WeeklyURBio-Rad]*26,[HealthBenefits]![medplan.Bi-WeeklyBio-Rad]*26) AS MedicalCostER, IIf([Ceridian]![UnionIndicator]="U" And [YearsService]>=3,[medplan]![Bi-WeeklyUREE]*26,[medplan]![Bi-WeeklyEE]*26) AS MEdicalCostEE

View 1 Replies View Related

Convert My ACCESS 2000 Db To MS SQL

Jul 31, 2007

I have an Access database and would like to convert it to an MS SQL database?!

View 4 Replies View Related

Convert Access Query To Sql

Aug 17, 2007

I am being asked to convert this access query into sql server 2000.
Access query
SELECT Left(Trim([Notes_Primary_Key]),InStr(Trim([Notes_Primary_Key])," ")) AS PcnPID, Trim([Notes_Secondary_Key]) AS PcnTicketNum
FROM tri_offnotes;

I'm new to SQL and am not familiar with which function replaces the InStr access function.

View 2 Replies View Related

Convert A Access Db To Sql 2005

Dec 16, 2007

what's the best way in sql 2005 to convert a access db?

View 1 Replies View Related

Convert From Access To SQL Server

Dec 28, 2006

Can I convert .MDB file to SQL Server database or I need to develop SQLserver database from begin, every table, every field?

View 2 Replies View Related

Convert Access Function To SQL

Jul 20, 2005

I'm going crazy trying to convert an Access Function to SQL.From what I've read, it has to be done as a stored procedure.I'm trying to take a field that is "minutes.seconds" and convert it to minutes.This is what I have in Access:Function ConvertToTime (myAnswer As Variant)Dim myMinutesmyMinutes-(((((myAnswer * 100)Mod 100/100/0.6)+(CInt(myAnswer-0.4))))ConvertToTime =(myMinutes)End FunctionWhen I tried to modify it in SQL:CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTimeFunction ConvertToTime(myAnswer As Variant)Dim myMinutesmyMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))ConvertToTime=(myMinutes)EndI get an error after ConverToTime.

View 2 Replies View Related

Convert An Access Db To SQLexpress

Oct 18, 2006

Hello to everybody,

I had just installed SQLserver Express. I'd like to know if tool to migrate an Access db to SQLexpress exists



Thank you

View 6 Replies View Related

Trying To Convert Query From T-SQL To Access

May 9, 2008

First off, I apologise if this is classed as off topic as it's concerning access but I couldn't see another forum that was better suited for this question.

I'm trying to run this query in Access. I designed it in SQL Management Studio with a test database that I set up and it runs fine. However, when I tried to run it in access I get a syntax error on the subquery bit. Can anyone tell me what I'm doing wrong here?

UPDATE tblInvoice
SET tblInvoice.PeriodID =
(SELECT p.PeriodID FROM tblPeriod p
INNER JOIN
tblInvoice i
ON i.TransDate BETWEEN p.PeriodStartDate AND p.PeriodEndDate
WHERE i.InvoiceID = tblInvoice.InvoiceID)

Thanks in advance,

Andy.

View 3 Replies View Related

Convert Access Query To Sql

Apr 10, 2007

how to convert this access query to sql query



IIf([Total Bunches] > 0, Production * 1000 / [Total Bunches], 0) as Name2

SUM(IIf(BlockInYield = -1, [SIZE], 0)) as Name1

IIf(BlockInYield = TRUE, IIf(TC_M > 0, TC_M, TC_DENS *[SIZE]), 0) as Name



please......

View 3 Replies View Related

How To Convert .btr File In SQL OR ACCESS

Apr 28, 2008

how To Convert .btr file in SQL OR ACCESS
please help me
tanks

View 4 Replies View Related

Convert SQL Express To SQL Server Or Access

Dec 15, 2006

Is there anyway to convert an SQL Express database to SQL server or Access format?

View 1 Replies View Related

How Do I Convert A Sql Server Database To Access

Oct 3, 2007

I have a sql server database and i need to convert to Access, how can i do this using sql server? 

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved