Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    starryNight is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    9

    Issue Using Replace in SQL String


    I am using Access 2007. Using VBA, I am trying to take a query that already exists and create a table from it. The query currently does not create a table (missing "INTO myTable" in SQL), so I am trying to use the Replace function. Here is the code I am using:

    Code:
    ' Some prior code such as Dims for db and rs exists above this code
     
    Dim strIn As String
    Dim strOut As String
    Dim strName As String
    Dim strQuery As String
     
    strQuery = "myTestQuery"
    strName = "tblMyTable"
     
    strIn = CurrentDb.QueryDefs(strQuery).SQL
    strOut = Replace(strIn, "From", " INTO [" & strName & "] FROM")
     
    Set rs = db.OpenRecordset(strOut, dbOpenSnapshot)
    When I run this code, an error is returned saying that it is an "Invalid operation" on the OpenRecordset line.

    I have done some debugging described below:

    1) If I run the query using strIn (SQL string without the replace), it runs fine.

    2) I have written out both strIn and strOut to the Immediate window to compare the strings and both look fine. In addition, if I copy the strings from the Immediate window into a new query, strIn runs fine.
    StrOut actually runs fine as well, with the exception that strOut is split in the middle of a field name and I have to delete the crLf that was created by the Immediate window inserting it for display purposes (it is a long string with a lot of joins). I have even tried replacing crLfs with spaces using the Replace function, to no avail.

    Does anyone have ideas on what the issue might be? I am stumped.
    Thanks!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    First thing I'd suggest is to put a Debug.Print "A " & strIn between your line 11 and 12

    and another Debug.Print "B " & strOut after your line 13

    and see what is happening to the values of strIn and strOut

    Good luck.

  3. #3
    starryNight is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    9
    Thank you orange for your suggestion.

    I might not have been clear about this in my original post, but in debugging item 2) I talked about writing "out both strIn and strOut to the Immediate window to compare the strings and both look fine." So, I have used Debug.Print, I just removed it here to be more succinct. The exception is that there are a lot of joins, so the "FROM" section of the string in the Immediate window is carried onto the next line. So, if I paste what was displayed for strOut in the Immediate window into a SQL query using Design View, I do have to hit backspace to remove the return that the Immediate window inserted. For some reason I do not have to do that with the strIn.

    The problem just got slightly more interesting. I just reran this part of the code so that I could provide an example of what the two strings look like, and when I pasted the strOut string into the SQL Design View for a query, it worked fine, with no removal of inserted return needed, yet I still get "Invalid Operation" when I try to use OpenRecordset on strOut (again, strIn works fine with OpenRecordset). Why would the same string work in Design View, but not with OpenRecordset?

  4. #4
    nicknameoscar is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2011
    Location
    Earlysville, VA
    Posts
    91
    Can you show here what strIn and strOut contain?

  5. #5
    starryNight is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    9
    Below are the strings that I copied from the Immediate window:

    strIn:
    Code:
    SELECT [00_Master tbl].[MM ID], [01_Measure tbl].[free text], lkup01b_Dis.[Th A], lkup01b_Dis.Dis, [00_Master tbl].[Title], [00_Master tbl].[Description], [00_Master tbl].[Type], [00_Master tbl].[Calculation], [lkup02_Source of Data Needed].[Source of Data Needed, [lkup05_D and S].[Developer], [lkup07_Sets].[Set Name], [00_Master tbl].[Endorsement Status], [lkup03_Settings].[Setting], lkup08_References.Reference, [lkup06_Implementation Programs].[Implementation Program]
    FROM ((((((((((((([00_Master tbl] LEFT JOIN [01_Measure tbl] ON [00_Master tbl].[MM ID]=[01_Measure tbl].[MM ID]) LEFT JOIN lkup01b_Dis ON [01_Measure tbl].[Dis Key]=lkup01b_Dis.lkup01b_ID) LEFT JOIN [05_D and s] ON [00_Master tbl].[MM ID]=[05_D and s].[MM ID]) LEFT JOIN [lkup05_D and S] ON [05_D and s].Developer=[lkup05_D and S].lkup05_ID) LEFT JOIN 08_References ON [00_Master tbl].[MM ID]=[08_References].[MM ID]) LEFT JOIN lkup08_References ON [08_References].Reference=lkup08_References.lkup01a_ID) LEFT JOIN [02_Source of data] ON [00_Master tbl].[MM ID]=[02_Source of data].[MM ID]) LEFT JOIN [lkup02_Source of Data Needed] ON [02_Source of data].Source=[lkup02_Source of Data Needed].lkup02_ID) LEFT JOIN [07_Measure sets] ON [00_Master tbl].[MM ID]=[07_Measure sets].[MM ID]) LEFT JOIN [lkup07_Sets] ON [07_Measure sets].[Set Key]=
    [lkup07_Sets].lkup07_ID) LEFT JOIN [03_Setting of use] ON [00_Master tbl].[MM ID]=[03_Setting of use].[MM ID]) LEFT JOIN [lkup03_Settings] ON [03_Setting of use].Setting=[lkup03_Settings].lkup03_ID) LEFT JOIN [06_Implementation programs] ON [00_Master tbl].[MM ID]=[06_Implementation programs].[MM ID]) LEFT JOIN [lkup06_Implementation Programs] ON [06_Implementation programs].[Impl Prog Key]=[lkup06_Implementation Programs].lkup06_ID
    WHERE ((([01_Measure tbl].[free text])="myKeyStr"));
    strOut:
    Code:
    SELECT [00_Master tbl].[MM ID], [01_Measure tbl].[free text], lkup01b_Dis.[Th A], lkup01b_Dis.Dis, [00_Master tbl].[Title], [00_Master tbl].[Description], [00_Master tbl].[Type], [00_Master tbl].[Calculation], [lkup02_Source of Data Needed].[Source of Data Needed, [lkup05_D and S].[Developer], [lkup07_Sets].[Set Name], [00_Master tbl].[Endorsement Status], [lkup03_Settings].[Setting], lkup08_References.Reference, [lkup06_Implementation Programs].[Implementation Program] INTO [tblMyTable] FROM ((((((((((((([00_Master tbl] LEFT JOIN [01_Measure tbl] ON [00_Master tbl].[MM ID]=[01_Measure tbl].[MM ID]) LEFT JOIN lkup01b_Dis ON [01_Measure tbl].[Dis Key]=lkup01b_Dis.lkup01b_ID) LEFT JOIN [05_D and s] ON [00_Master tbl].[MM ID]=[05_D and s].[MM ID])
    LEFT JOIN [lkup05_D and S] ON [05_D and s].Developer=[lkup05_D and S].lkup05_ID) LEFT JOIN 08_References ON [00_Master tbl].[MM ID]=[08_References].[MM ID]) LEFT JOIN lkup08_References ON [08_References].Reference=lkup08_References.lkup01a_ID) LEFT JOIN [02_Source of data] ON [00_Master tbl].[MM ID]=[02_Source of data].[MM ID]) LEFT JOIN [lkup02_Source of Data Needed] ON [02_Source of data].Source=[lkup02_Source of Data Needed].lkup02_ID) LEFT JOIN [07_Measure sets] ON [00_Master tbl].[MM ID]=[07_Measure sets].[MM ID]) LEFT JOIN [lkup07_Sets] ON [07_Measure sets].[Set Key]=[lkup07_Sets].lkup07_ID) LEFT JOIN [03_Setting of use] ON [00_Master tbl].[MM ID]=[03_Setting of use].[MM ID]) LEFT JOIN [lkup03_Settings] ON [03_Setting of use].Setting=[lkup03_Settings].lkup03_ID) LEFT JOIN [06_Implementation programs] ON [00_Master tbl].[MM ID]=[06_Implementation programs].[MM ID]
    ) LEFT JOIN [lkup06_Implementation Programs] ON [06_Implementation programs].[Impl Prog Key]=[lkup06_Implementation Programs].lkup06_ID WHERE ((([01_Measure tbl].[free text])="myKeyStr"));

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I looked at your strIn and think I see a problem in this line

    WHERE ((([01_Measure tbl].[free text])="myKeyStr"));
    I don't think you want
    ="myKeyStr"
    I think you want the value of MyKeyStr so the line would be

    ... WHERE ((([01_Measure tbl].[free text])='" & myKeyStr & "'"

  7. #7
    starryNight is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    9
    Hi oragnge. Thank you again for looking at this. In this particular case "myKeyStr" is truly a string, not a variable holding a string value. I changed the name of the real string in this case to sanitize it for public display. You will see that it is the same format as shown in the strIn section, and that works fine.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    OK, so is everything working?

  9. #9
    starryNight is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    9
    No, everything is the same as it was in the original post. If I paste strIn into a query (SQL Design View), it works fine. In addition, strOut works fine most of the time when I paste it into a query, though occasionally I will have to delete the carriage return that the Immediate window inserts for display purposes. The problem is, I don't want to have to paste the string into the SQL view to build the query. I want to do it automatically (OpenRecordset method), but this only works properly for strIn. Running the query using OpenRecordset does not work for strOut, only strIn.

    Thanks.

  10. #10
    nicknameoscar is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2011
    Location
    Earlysville, VA
    Posts
    91
    Below is quoted from a book by Julitta Korol (excellent book in my opinion):

    "Snapshot type - Used to access records from a local table stored in a Microsoft Access database as well as any linked table or a query. Snapshot recordsets contain a copy of the records in RAM (random access memory) and provide no direct access to the underlying data. They are used for reading data only - you can't use them to add, update or delete records."

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Have you tried:

    db.Execute strOut, dbFailOnError

    I assume you've set db at some point? You didn't use it 2 lines above the recordset line.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    starryNight is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    9
    Hi pbaldy. Yes, I don't think I was clear about that, but the Dim and Set of the db was done in code above the snippet I provided in the original message. I know that part is OK because it works if I pass strIn into OpenRecordset (Set rs = db.OpenRecordset(strOut, dbOpenSnapshot)), but not when I do the same with strOut.

    By the way, here is the code that dims and sets the db:
    Dim db As Database
    Dim rs As Recordset

    'Open connection to current Access database

    Set db = CurrentDb()

  13. #13
    starryNight is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    9
    Hi Oscar. That is a very interesting point you make about a snapshot type. I can't see why that would work for one string, but not the other. Also, I tried each of the RecordsetTypes and none worked. This is a stumper. :/

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I found a post saying you couldn't use a querydef with a maketable??
    I tried to duplicate your issue with one of my own queries. I got it to work, but maybe not as you intended.

    I used the existing query's sql and the Replace function to build a new querydef. This would be a temporary query in my terms, called "Tquery"

    You then execute that query to create your makeTable.

    Then you open the new Table as the recordset and process it as you wish.

    Here's the code, but I'm referencing my table and query names. I used your intro code.
    Sub starryNight()
    Dim strIn As String
    Dim strOut As String
    Dim strName As String
    Dim strQuery As String
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    On Error GoTo starryNight_Error

    strQuery = "allbooksallrooms" '"myTestQuery"
    strName = "tblMyTable"

    strIn = CurrentDb.QueryDefs(strQuery).SQL
    Debug.Print strIn
    strOut = Replace(strIn, "From", " INTO [" & strName & "] FROM")
    Debug.Print strOut
    Set db = CurrentDb
    DoCmd.DeleteObject acQuery, "TQuery" 'delete this since it will exist after program runs
    Set qdf = db.CreateQueryDef("TQuery", strOut)

    DoCmd.OpenQuery ("tquery")
    Set rs = db.OpenRecordset(strName)
    Do While Not rs.EOF
    'do something to show the rs is opened and accessible
    Debug.Print rs.Fields(3), rs.Fields(1)
    rs.MoveNext
    Loop

    On Error GoTo 0
    Exit Sub

    starryNight_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure starryNight of Module AWF_Related"
    End Sub

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    After reviewing your code now, having gotten something to work, it seems wrong to use/open a MakeTable query as a recordset. By definition the maketable query is going to build a table; then you open that table as a recordset (set of records ) to be processed in a loop.

    When I try to use the maketable as the source of a recordset it gives me a 3219 error.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replace "
    By jgelpi16 in forum Programming
    Replies: 1
    Last Post: 05-10-2011, 07:09 AM
  2. replace a character with a wildcard
    By neeedhelp in forum Programming
    Replies: 2
    Last Post: 04-11-2011, 05:02 PM
  3. auto replace??
    By d3pl0y3d in forum Access
    Replies: 2
    Last Post: 02-11-2011, 02:09 PM
  4. Replace a string/text in ms-access db
    By anziga in forum Queries
    Replies: 4
    Last Post: 12-31-2010, 06:40 PM
  5. replace characters in a string
    By blazixinfo@yahoo.com in forum Access
    Replies: 6
    Last Post: 08-06-2009, 03:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums