Converting Access DB Code To Tsql--Help!!!!!

May 3, 2001

We are migrating an access97 database to sql server7.0. there are queries in access which need to be made into tsql queries.And what are the steps to convert access97 to sql7.0 and how do i migrate memo fields to sql7. Is there some method to convert or tool that does that...any help is welcome and thanks in advance.

View 1 Replies


ADVERTISEMENT

Help Required Converting From MS Access SQL To TSQL

May 20, 2008

Hello, I am fairly new to SQL Server so I apologise if this is the wrong forum. I have a Sales analysis table in a SQL Server 2000 database. The table is populated from various sources in our ERP system. via a DTS package For our French branch sales unit of measure is eachs (EA) for actuals, but the primary UOM and our forecast data is normally in cartons. I have a product master table which defines primary unit of measure, and a unit of measure conversion table. So if I wanted to convert the data all to the primary measure I would write the below in Access:

UPDATE (tblSalesReport INNER JOIN tblItemMasterERP ON tblSalesReport.fldProductNo = tblItemMasterERP.fldProductNo) INNER JOIN tblUOMConvertERP ON (tblItemMasterERP.fldShortItemNo = tblUOMConvertERP.fldItemNo) AND (tblItemMasterERP.fldPrimaryUOM = tblUOMConvertERP.fldUOM1) SET tblSalesReport.fldUOM = [tblItemMasterERP]![fldPrimaryUOM], tblSalesReport.fldQuantity = [tblSalesReport]![fldQuantity]/[tblUOMConvertERP]![fldConvFactor]
WHERE (((tblSalesReport.fldCompany)="00007") AND ((tblUOMConvertERP.fldUOM2)=[tblSalesReport]![fldUOM]) AND (([tblSalesReport]![fldUOM])<>[tblItemMasterERP]![fldPrimaryUOM]));

I have found that in the DTS I can add an SQL task, but it seems to only allow UPDATE if there are no joined tables. I found the same thing in Stored Procedures, the SQL designer would only allow me to use one table. I guess I am looking in the wrong places. Can anyone point me in the right direction to incorporate the above sql (or equivolent) into our DTS package. Unfortunately the company decided to dispense with the services of the person who designed the package.

View 6 Replies View Related

Help With Converting Code: VB Code In SQL Server 2000-&&>Visual Studio BI 2005

Jul 27, 2006

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

View 7 Replies View Related

Converting Oracle SQL To TSQL

Jul 20, 2005

Hello,I am new to sql*server, but have used Oracle for years. Can anyonerecommend a good online guide for converting all the Oracle SQL I knowto TSQL?tia,Mike

View 1 Replies View Related

How To Debug Tsql Code?

Oct 6, 2007

Hi everybody

Could anyone tell me how i can Trace or debug my TSQL Script
like High level Programming Languages line by line? is there any way to do that at all?

Kind Regards.

View 1 Replies View Related

Trying To Hide/obfuscate TSQL Code

Sep 24, 2006

Hi there,

we are porting a huge PL/SQL(on Oracle 10g) application to TSQL (SQL2005) right now and we will offer a test drive for download. In Oracle we could "obfuscate" the PL/SQL code with a hash, so nobody can see the PL/SQL statements after the encryption. Is there something like that for SQL Server 2005 as well which can not be overidden by a local admin (like with changing a password for example)? We have spent 5 years in writing SQL code (it is huge) and don't really want to spread the source all over the internet. Any help is appreciated.

Niko

View 8 Replies View Related

Converting Some VBA Code To SQL Function

Sep 6, 2006

Hi,
I'm new to this SQL thing, and I need some help with s stored procedure/function.
First, the scenario:
We are a social service agency, and like all such organizations, we have requirements for perodic reports about our clients. There are lots of them, but if I figure how to do one, I think I can apply the theory to the others.

First, this is some sample VBA code I've tested and which proved satisfactory for the basic task, just for a form. It is passed a date (dteDOP), then adds 3 months, (quarterly report)and loops until it exceeds the current date, thus generating the a due date for the the next report.

'initialize the due date
dteDueDate = dteDOP

'Add quarterly intervals, starting with (DOP + 3 months), until
'you exceed today's date
Do Until dteDueDate > Date
dteDueDate = DateAdd("m", 3, dteDueDate)
Loop
'set a text box to the next due date after today
Me.txtNextQuarDue = dteDueDate

Obviously, this isn't really a function at the moment, but it worked as a test of the logic, with instantly visible results.

This works, but I'd like to do it on the server end, so I can send out notifications. How would this be accomplished as an SQL stored procedure/function? Obviously, for that I'll need to again do a comparison of the current date with the due date for timing concerns, but that should be relatively simple. Also, I know that the '@' symbol is somehow part of variables in SQL Procedures/functions, could you give me a very basic explanation of this, especially the difference between @xxx and @@xxx?

Thanks in advance,
Stephen

-----------------------------------------------
-----------------------------------------------
Gary Getsum: What happened to my mule?
DM: It's dead; It got stung by a giant wasp.
Gary: Can't I heal it?
DM: I'm sorry, I know you were fond of the mule, but it just got attacked by a wasp the size of a Volkswagen!
Gary: So?
DM: Dude, look- you're going to have to carry all your own treasure now... Your mule gives new meaning to the term "Puff Daddy".

View 17 Replies View Related

TSQL From An Access SQL Statement

Feb 21, 2001

Good morning one and all,

I have some queries that were written in access that I need to port into SQL 7, the whole process is boring and mundane. Does any1 know of a translator (i.e. access sql to t-sql) or a reference to the differences between access SQL and t-Sql.

Any and all help appreciated,

Thanx Gurmi

View 1 Replies View Related

Problem Converting VS2003 Code To VS2005

Aug 14, 2005

Hi:

View 7 Replies View Related

MS Access SQL Migration To MS SQL Server TSQL

Jun 22, 2006

got some MS Access SQL Code that needs converting into TSQL:

SELECT dbo_qryMyServices.FormsServiceID,
dbo_qryMyServices.ServiceName, Sum(IIf(IsNull([CompletionDate]),0,1))
AS Completed,
Count([pkServiceID])-Sum(IIf(IsNull([CompletionDate]),0,1)) AS
Uncompleted, Count(dbo_MyServiceRequests.pkServiceID) AS TotalCount
FROM dbo_qryMyServices LEFT JOIN dbo_MyServiceRequests ON dbo_qryMyServices.FormsServiceID = dbo_MyServiceRequests.PostType
GROUP BY dbo_qryMyServices.FormsServiceID, dbo_qryMyServices.ServiceName
ORDER BY dbo_qryMyServices.ServiceName;


because it's Access and got VBA stuff in it - IIF and ISNULL, I can't figure out how to make it work in proper SQL.

Any help would be most appreciated!

View 2 Replies View Related

How To Access Global Variable In DTS And Use In TSQL

Mar 21, 2002

I have a VBscript below which works fine, which creates a unique file name for a text source and creates it just fine. What I need is to use that file name in an insert statement in TSQL to load a record to a transaction table. How do I utilize this variable to do this?
Thanks

Function Main()
Dim oConn, sFilename

' Filename format - exyymmdd.log
sFilename = "ex" & Right(Year(Now()), 2)
If Month(Now()) < 10 Then sFilename = sFilename & "0" & _
Month(Now()) Else sFilename = sFilename & Month(Now())
If Day(Now()) < 10 Then sFilename = sFilename & _
"0" & Day(Now()) Else sFilename = sFilename & Day(Now())
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename & ".log"

Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)")
oConn.DataSource = sFilename
' oConn.DataTarget = sFilename
Set oConn = Nothing

Main = DTSTaskExecResult_Success
End Function

View 3 Replies View Related

TSQL - Retrieve All Columns In My MS Access Table Less Two Of Them

Apr 6, 2008

Hi guys,
Working on a MS Access database, I have a table named "myTable" which contains several fields.
I just want to retrieve all the fields (columns) in the myTable, without retrieving Col1 and Col2
What should my SQL string be?

SELECT * (not Col1, Col2) FROM myTable

Thanks in advance for any help.
Aldo.

View 5 Replies View Related

Help With TSQL Stored Procedure - Error-Exec Point-Procedure Code

Nov 6, 2007

I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...


Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24

Error converting data type varchar to numeric.

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...


EXEC [dbo].[sp_SearchCandidatesAdvanced]

@LicenseType = 4,

@PositionType = 4,

@BeginAvailableDate = '10/10/2006',

@EndAvailableDate = '10/31/2007',

@EmployerLatitude = 29.346675,

@EmployerLongitude = -89.42251,

@Radius = 50

GO

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go



ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]

@LicenseType int = 0,

@PositionType int = 0,

@BeginAvailableDate DATETIME = NULL,

@EndAvailableDate DATETIME = NULL,

@EmployerLatitude DECIMAL(10, 6),

@EmployerLongitude DECIMAL(10, 6),

@Radius INT


AS


SET NOCOUNT ON


DECLARE @v_SQL NVARCHAR(2000)

DECLARE @v_RadiusMath NVARCHAR(1000)

DECLARE @earthRadius DECIMAL(10, 6)


SET @earthRadius = 3963.191


-- SET @EmployerLatitude = 29.346675

-- SET @EmployerLongitude = -89.42251

-- SET @radius = 50


SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius




SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +

'Round(' + @v_RadiusMath + ', 0) AS Distance ' +

'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +

'WHERE ' + @v_RadiusMath + ' <= ' + @Radius


IF @LicenseType <> 0

BEGIN

SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType

END


IF @PositionType <> 0

BEGIN

SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType

END


IF LEN(@BeginAvailableDate) > 0

BEGIN

SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate

END


--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'


PRINT(@v_SQL)

EXEC(@v_SQL)


-----------------------------------------------------------------------------------------------------------------

View 4 Replies View Related

Is There A TSQL Statement For Importing A Singular Table Into Sql Server 2005 From Access?

Jan 18, 2008

Hello all.

I was wondering if there was a simple Import statement I could use in SQL to import an Access Table into SQL Server 2005.

I know how to use the SSIS Import/Export Wizard, but that seems excessive to import a single 204 record table

Any help on this would be greatly appreciated.

View 3 Replies View Related

Converting Access SQL To T-Sql

Oct 15, 2007

Any help converting the following sql to T-Sql would be helpful. I created it in Access ant works great but cant get the case to work. Need to put it into a accounting program that uses T-Sql. The purpose it to come up with a new field called STATUS based on key words in the "decoded" column.

Thanks!

Status: IIf([TableName]![ColumnName] Like "*PA'D*","PA'D",IIf([TableName]![ ColumnName] Like "*SOLD*","SOLD",IIf([TableName]![ ColumnName] Like "*DNU*","DNU","ACTIVE")))

View 2 Replies View Related

Converting MS ACCESS Db To SQLExpress

Nov 10, 2005

Is it possible to convert an ms access mdb file to an sqlexpress mdf file?Any help appreciated,Henk Feijt

View 7 Replies View Related

Converting Access Databases To SQL

Apr 18, 2001

what is the proper method of taking a database made in MS Access and converting it so that it runs in SQL Server 7?

View 1 Replies View Related

Converting Access 97 To SQL7

Jul 23, 2001

I am trying to convert the following Access 97 query into transact SQL for SQL Server 7 but can't seem to get the syntax correct.

"UPDATE datInventory INNER JOIN tmpInventoryReport ON (datInventory.DenomID = tmpInventoryReport.Denomination) AND (datInventory.ReportDate = tmpInventoryReport.ReportDate) AND (datInventory.RegionID = tmpInventoryReport.Region) AND (datInventory.ACCID = tmpInventoryReport.Carrier) AND (datInventory.OwnerID = tmpInventoryReport.[Financial Institution])
SET tmpInventoryReport.[Working Inventory] = [datInventory].[WIBalance], tmpInventoryReport.Surplus = [datInventory].[DSBalance];"

Can anyone help?

View 1 Replies View Related

Converting Yes/No, Access Field To SQL

Oct 24, 2000

I did create the field on table as TinyInt. I created an appending query and appended the records to the SQL table. Now I have 0's or 255's in the field.
Shouldn't they be 0's and 1's instead????
What am I doing wrong?
What's the best way to convert the Yes/No fields into SQL, since I want to keep the access front end.
Thanks for any help.

View 2 Replies View Related

Converting Query From Access

Dec 6, 2007

Hi,I would like some help converting an access query to a SQL Server query.The access query is made up of the following and then repeated for each field:SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...FROM dbo.applicants I have tried using the following to test out an alternative, but it brings back the incorrect figure:SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleFROM dbo.applicants I've looked at the table and should get back 350, but only get back 193.But using the following query I get the correct figure:SELECT COUNT(gender) AS FemaleFROM applicantsGROUP BY genderHAVING (gender = 'Female') Although I can't use the above query because I want to also count how many 'Male' applicants there are.How can I do this?Thanks

View 5 Replies View Related

Converting A View From Access

Nov 17, 2014

I'm converting a View from access to Sql and I'm stuck on this IF statement.

IIf([FG_Qtys_1].[CoreQty]=0 Or [FG_Qtys_1].[CoreQty] Is Null,[KitCoreOnHand],[FG_Qtys_1].[CoreQty]);

I know that I could use CASE statement but i keep on getting errors.

My Case Statement:
case when [dbo.FG_Qtys_view].[CoreQty]=0 then dbo.FG_Qtys_Kits.KitCoreOnHand
when [dbo.FG_Qtys_view].[CoreQty] ISNULL then [dbo.FG_Qtys_view].[CoreQty]
end as CoreQty

View 5 Replies View Related

Converting Access To Server

Apr 13, 2015

I'm currently using Management Studio 2008 R2 and struggling as I am very raw to SQL full stop eg a complete newbie to it.

WHERE (((dbo_ED_ATTENDANCE.AttendDateTime)>=Date()-IIf(Weekday(Date(),2)<2,4,2) And (dbo_ED_ATTENDANCE.AttendDateTime)<=Date()-1)

The above criteria is cut from an existing Access query which I am trying to replicate in SQL Management Studio 2008 R2.

View 1 Replies View Related

Converting Access App To Sql Server

Dec 14, 2005

Hope this makes sense.
I am trying to convert an Access based blog app to SQL Server but I'm having some trouble with some SQL.

The sql is as follows:
SELECT *, (SELECT COUNT(*) FROM tblComment WHERE tblComment.blogID = tblBlog.BlogID AND tblComment.commentInclude <> 0) AS TOTAL_LINKS
FROM joinBlog
WHERE BlogIncluded <> 0
ORDER BY BlogID DESC

The access version returns blog entries & the number of comments posted to each entry.

joinBlog is an Access query:
SELECT tblBlog.BlogID, tblBlog.CatID AS tblBlog_CatID, tblBlog.BlogHeadline, tblBlog.BlogHTML, tblBlog.BlogDate, tblBlog.BlogIncluded, tblCategory.catID AS tblCategory_catID, tblCategory.catName
FROM tblCategory RIGHT JOIN tblBlog ON tblCategory.catID = tblBlog.CatID;

I assume I need to make a view out of the Access query, I have done this & that appears to work.

The problem I have is when I try the 1st sql that is in my page with sql server I get the following error:
The column prefix 'tblBlog' does not match with a table name or alias name used in the query.

I can make the following change which returns data but does not attach the blog comment counts to the proper blog entry, instead it returns the total comments in the query:
SELECT *, (SELECT COUNT(*) FROM tblComment,tblBlog WHERE tblComment.blogID = tblBlog.BlogID AND tblComment.commentInclude <> 0) AS TOTAL_LINKS
FROM joinBlog
WHERE BlogIncluded <> 0
ORDER BY BlogID DESC

Can anyone tell me how to convert this for SQL Server? This is my 1st access to sql server attempt.
Thanks.

View 3 Replies View Related

Converting Access To MS SQL Server

Feb 23, 2007

I'd like to convert my Access database table to MS SQL Server 2005 Express.
I have a text field and a memo field.
What are the corresponding datafield types for SQL Server?

thanks.

View 1 Replies View Related

Converting Access Db To Mysql

Jul 20, 2005

Hey people,I have to convert MS Access 2000 database into mysql database, the wholething being part of this project I'm doing for one of my facultyclasses. My professor somehow presumed I knew db's and gave me long listof things to do with that particular database, first thing being thatparticular conversion. Truth is that I don't know a first thing aboutdb's, let alone using mysql... I downloaded mysql form www.mysql.com andstill searching for MS Access 2000 (it doesn't work with 2003 I have,or I don't know how to make it work).Any kind of help will be welcomed and highly appreciated!!!Thanks,Mario

View 17 Replies View Related

Converting ACCESS And EXCEL Data To SQL

May 14, 2005

Hi,
I have some tables in an ACCESS database, and would like to recreate them in a SQL2005 databse.How may this be done?I am able to create a Data Component with the ACCESS mdb file.
Likewise, how may I convert EXCEL data to SQL2005 table?Thanks.
David
 

View 3 Replies View Related

Access 97 DB And SQL Server 7 - Converting Nvarchar To Int

May 9, 2001

I need help converting an nvarchar value to int. I receive a SQL error when running the following query...

"SELECT DISTINCT [Time Cards].TimeCardID, [Time Cards].Status, [Time Cards].Verification, [Time Card Hours].[Date Worked], [Time Card Hours].[Billable Hours], [Time Card Hours].[Billing Rate], [Time Card Hours].[Overtime Hours], [Time Card Hours].[Overtime Rate], Employees.FirstName, Employees.LastName, [Function].[Function], [Time Card Hours].[Invoiced Hours], [Time Card Hours].[Invoiced Overtime], [Time Cards].[30 Day Grace]

FROM [Function] INNER JOIN (Employees INNER JOIN ([Time Cards] INNER JOIN [Time Card Hours] ON [Time Cards].TimeCardID = [Time Card Hours].[Time Card ID]) ON Employees.EmployeeID = [Time Cards].EmployeeID) ON [Function].FunctionID = Employees.FunctionID

WHERE ((([Time Cards].[30 Day Grace])=-1));"

[Time Card Hours - nvarchar, Time Cards - int]

View 2 Replies View Related

Error In Query Converting From Access To SQL

Dec 7, 1999

Hi,

I'm using a database through ODBC in an application designed on Macromedia Drumbeat 2000, and use the following query for one of the segments:

SELECT DISTINCTROW Books.ISBN, Books.Title, Books.Category, Books.Description, Books.Price, Books.Pubdate, Books.Keywords, Books.UnitWeight, Authors.au_lastname, Authors.au_firstname, Authors.au_midname, Publishers.pub_name, Books.Pub_No
FROM Publishers INNER JOIN (Authors INNER JOIN Books ON Authors.au_id = Books.Au_No) ON Publishers.pub_id = Books.Pub_No

It works fine if I use the MS Access version of the database through ODBC, but if I try using the SQL version, I get the following error for this query:

Line 1: Incorrect syntax near '.'. Statement(s) could not be prepared.

Why on earth is this happenning? I'm completely at my wits end...and pointers would be wonderful.

Thanks...

View 1 Replies View Related

Help Converting Query From Access To Sql 2000

Aug 10, 2007

This query from access does not work in sql server 2000. How do I write this in sql to run?

SELECT First(tri_ProcMast.ddesc) AS FirstOfddesc, tri_ProcMast.proccd
FROM tri_ProcMast
GROUP BY tri_ProcMast.proccd;

View 2 Replies View Related

Converting IIF In Access Query To SQL Server

Jul 20, 2005

I am trying to upsize a database to SQL server (on which I am a novice). InAccess as part of a much more complex query I had the following (from sqlview)SELECTIIf(InStr([ItemName],"*")>0,Left([ItemName],InStr([ItemName],"*")-1),[ItemName]) AS ShortName FROM corp_infoWhich gives a return value for the whole of ItemName if there is no star init, or the portion up to the star if there is a starI am having a nightmare trying to get an equivalent in SQL server. I'veworked out that Instr is charindex in sql and can adjust for that, but can'twork out how to get a conditional select statement working.It may well be obvious, but any help much appreciated. Thanks.Robin Hammondwww.enhanceddatasystems.com

View 1 Replies View Related

Help With Converting Access VBA To Appropriate SSIS Solution

Mar 20, 2007

Hi

I have created a package which imports a unit of measure table. I now want to populate more rows in the same table from itself. In Access VBA the below code extract does the first part of the job I need to do. Can anyone point me in the right direction to convert this into an efficient SSIS solution.
SQLstr =€?SELECT [UOM conv].[Short Item No], [UOM conv].UOM, Count([UOM conv].[UOM 2]) AS [CountOfUOM 2] €œ & _
€œFROM [UOM conv] €œ & _
€œGROUP BY [UOM conv].[Short Item No], [UOM conv].UOM €œ & _
€œHAVING (((Count([UOM conv].[UOM 2]))>1)); €œ

Set RsMoreThanOne = db.OpenRecordset(SQLstr)
With RsMoreThanOne
While Not .EOF
SQLstr = "SELECT [UOM conv].* FROM [UOM conv] WHERE ((([UOM conv].[Short Item No])=" & ![Short item No] & ") AND (([UOM conv].UOM)='" & !UOM & "'));"

Set RsSql = db.OpenRecordset(SQLstr, dbOpenSnapshot)
RsSql.MoveLast
x = RsSql.RecordCount
Set rs = db.OpenRecordset("UOM Conv")
rs.Index = "PrimaryKey"
RsSql.MoveFirst
Item = RsSql![Short item No]
For y = 1 To x
Um1(y) = RsSql![UOM]
Um2(y) = RsSql![uom 2]
Fct(y) = RsSql!factor
RsSql.MoveNext
Next y
For y = 1 To x - 1
For k = 1+y To x
rs.Seek "=", Item, Um2(y), Um2(k)
If rs.NoMatch Then
rs.AddNew
rs![Short item No] = Item
rs!UOM = Um2(y)
rs![uom 2] = Um2(k)
rs!factor = Fct(k) / Fct(y)
rs!calculated = True
rs.Update
Else
If rs!calculated = True Then
rs.Edit
rs!factor = Fct(k) / Fct(y)
rs.Update
End If
End If
rs.Seek "=", Item, Um2(k), Um2(y)
If rs.NoMatch Then
rs.AddNew
rs![Short item No] = Item
rs!UOM = Um2(k)
rs![uom 2] = Um2(y)
rs!factor = Fct(y) / Fct(k)
rs!calculated = True
rs.Update
Else
If rs!calculated = True Then
rs.Edit
rs!factor = Fct(y) / Fct(k)
rs.Update
End If
End If
Next k
Next y

RsSql.Close
.MoveNext
rs.Close
Wend
.Close
End With
db.Close

Regards

ADG

View 3 Replies View Related

Converting A MS Access Query To SQL Stored Procedure

Nov 9, 2001

I am switching my database from MS access to SQL server, and i want the following query to br converted to SQL stored procedure

CREATE PROCEDURE FORUM_MESSAGE AS
SELECT *
FROM FORUM_MESSAGES
WHERE ID=MessageID;

here "MessageID" is a run time generated parameter, and is not a field in the database.

thanx

View 1 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







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