Solved

Basic steps to change Access to SQL

Posted on 2008-10-03
6
172 Views
Last Modified: 2012-05-05
I have access code that does the following
1) I run a Make table query filtering the data based on what the users enter and create table1
2) Table1 has all the fields I need to run the report as well as 1 blank field.
3) Then I run another Make table query filtering the data based on what the users enters to get all the records with 'rooms' and thats in table2
4) In my code I read table 2, I gather all the rooms in a string and I update the blank field in table1 with the string.
Now I have all I need to run the report. It all works fine, but I'm doing this in access and it's my understanding since I'm linked to SQL server that it would be faster to take more advantage of SQL code.
So based on the above, how do I do that?
1) I created a pass through query that created a table in sql.  Since you can't filter using a pass thru query and you can't create a blank field (if you can do either please tell me how) I'm pretty
much just creating a copy of the original table because nothing is filtered out.  Obviously there's a better way.
In general what would be my steps?
Thanks
0
Comment
Question by:BobRosas
  • 3
  • 3
6 Comments
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
First, you can certainly add a blank fields and filters in a passthru query.   But before going there, my hunch is that we can probably help write a query that returns the result set you want without the make table steps.

We need to see your access queries to move forward on this.

 
0
 

Author Comment

by:BobRosas
Comment Utility
Does this mean I'll be able to update the 'result set' with the room info (step 4 above)?
I've attached my query but not the code I use to update the make table with rooms.  
Just let me know if you need more code!
Thank you so much!  
SELECT DISTINCT tblDaily.DailyId, tblDaily.QCReportNo, tblDaily.VersionId, tblDaily.DailyDate, tblWorkRelease.Facility, tblWorkRelease.Elevation, tblWorkRelease.Area, tblControl.[SubContract No], tblDaily.AmbientTaken, tblDaily.ActionTaken, tblDaily.Comments, tblDaily.CertifiedBy, tblDaily.Supervisor, tblDaily.Shift, tblControl.[Project No], tblControl.[Project Name], "" AS RoomByVrsn INTO tblMTDaily

FROM tblControl RIGHT JOIN (tblWorkRelease RIGHT JOIN (tblQCReportNo RIGHT JOIN tblDaily ON tblQCReportNo.QCReportNo = tblDaily.QCReportNo) ON tblWorkRelease.WorkReleaseId = tblQCReportNo.WorkReleaseID) ON tblControl.ControlId = tblWorkRelease.ControlId

WHERE (((tblWorkRelease.WorkReleaseId)=[Forms]![frmPrevDailyDFT]![txtReleaseNo]));

Open in new window

0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
>Does this mean I'll be able to update the 'result set' with the room info (step 4 above)?

Piece of cake.  

I need to know what you are filtering in Step 3
I need to know the details of the string you are constructing for Step 4



0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:BobRosas
Comment Utility
I apologize for my long delay.  We had a power outage on Friday...need I say more.

Anyway, the query I use to filter the data in step 3 is attached.

The code for how I get the string in Step 4 is also attached.
In short what I'm doing is looping thru the records (query in step 3) and creating a string with roomNo until QCReport No and TestDescription no longer matche.  Then I save the created string to the blank field in Table1 (from Step 1 & 2) to the record where the QCReportNo and TestDescription match.

Step 3 filter is...

SELECT DISTINCT tblDaily.QCReportNo, tblDaily.VersionId, tblWorkRelease.WorkReleaseId, tblTestsPerformed.TestDescription, tblRooms.RoomNo, tblTestsPerformed.Results

FROM tblWorkRelease RIGHT JOIN (tblQCReportNo RIGHT JOIN (tblDaily INNER JOIN (tblTestsPerformed INNER JOIN tblRooms ON tblTestsPerformed.TestDescId = tblRooms.TestDescId) ON tblDaily.DailyId = tblTestsPerformed.DailyId) ON tblQCReportNo.QCReportNo = tblDaily.QCReportNo) ON tblWorkRelease.WorkReleaseId = tblQCReportNo.WorkReleaseID

WHERE (((tblWorkRelease.WorkReleaseId)=[Forms]![frmPrevDailyDFT]![txtReleaseNo]))

ORDER BY tblDaily.VersionId, tblTestsPerformed.TestDescription, tblRooms.RoomNo;
 
 

Step 4 code

Set rs = qdf.OpenRecordset

    Set rs2 = CurrentDb.OpenRecordset("tblMTTestRooms", DB_OPEN_DYNASET)

    With rs

        Do While Not .EOF

        strTestDescription = rs!TestDescription  'this saves off test description

        strTestDescriptionCur = rs!TestDescription 'this keeps field so when eof code still works

        strRoomNo = strRoomNo & rs!RoomNo & ", "

        rs.MoveNext

        strTestDescriptionCur = rs!TestDescription 'this keeps field so when eof code still works

        If strTestDescription <> strTestDescriptionCur Then

            With rs2

                .Edit

                !RoomNo = Left$(strRoomNo, Len(strRoomNo) - 2)  'deletes trailing comma

                .Update

                rs2.MoveNext

                strRoomNo = ""

            End With

        Else

        End If

        Loop

    End With

Open in new window

0
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
Comment Utility
Wow! You do realize that VBA code is likely to update the wrong row in tblMTTstRooms?  I mean there is nothing in the code that establishes a relationship between the RoomNo's you are concatenating and the row you are updating????  But I digresss...

The better way to do that in SQL Server is to concatenate your RoomNo's in a function.  Then you can update the table directly using the function.  The attached code is not exact because I do not know all the column names in your table, but it should give the idea.  You also need to add additional parms to the function if you want to break on QCReportNo  (I did not see a break like that in your VBA Code!).








 
Update YOURTABLE

   Set RoomNo=dbo.ConcatRoomNo(TestDescription)
 

Create dbo.function (@TestDescription varchar(max))

  varchar(max)

as

BEGIN

  DECLARE @ANS VARCHAR(MAX)

  SELECT @ANS = ISNULL(@ANS+ ', ','')  + RoomNo

    FROM yourtable WHERE TestDescription=@TestDescription

RETURN @ANS

END
 
 
 
 
 
 
 

 

Open in new window

0
 

Author Closing Comment

by:BobRosas
Comment Utility
You are correct about updating the wrong row.  I'm going to try and fix that now so I'll close this question and post more later.
Thanks again.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now