• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

Basic steps to change Access to SQL

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?
  • 3
  • 3
1 Solution
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.

BobRosasAuthor Commented:
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

>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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

BobRosasAuthor Commented:
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 & ", "
        strTestDescriptionCur = rs!TestDescription 'this keeps field so when eof code still works
        If strTestDescription <> strTestDescriptionCur Then
            With rs2
                !RoomNo = Left$(strRoomNo, Len(strRoomNo) - 2)  'deletes trailing comma
                strRoomNo = ""
            End With
        End If
    End With

Open in new window

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

   Set RoomNo=dbo.ConcatRoomNo(TestDescription)
Create dbo.function (@TestDescription varchar(max))
  SELECT @ANS = ISNULL(@ANS+ ', ','')  + RoomNo
    FROM yourtable WHERE TestDescription=@TestDescription

Open in new window

BobRosasAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now