Problem with changing tablenames in queries with SQL 1

[rloibl]

Hello
I have managed to change table names for a query through a form so the user can enter which table should be shown in a defined query. Now I can change the table but also the criteria that I put in is lost.
So I need a way that the tables will change but the criteria will still be there after the change because the tables all have the same field names there are only different table names.

Regards
Robert

 

[downwitchyobadself]

Say the user is choosing the tbl nm from cboMyTbl, qry nm from cboMyQuery. For this to work, no field names must equal table names, and the field names must be literally the same in every table and query. Use this in the form's class module:

Private Sub Rewrite_Qry_For_Tbl()

   Dim strLastTbl as string
   Dim strSQL as String
   Dim qdf    as DAO.QueryDef
   Dim i      as Integer
   Dim j      as Integer


   
   Set qdf = Currentdb.QueryDefs(cboMyQuery)

   strSQL = qdf.SQL
   i = Instr(1, strSQL, "FROM ")
   i = i + 5
   j = InStr(i, strSQL, " ")
   strLastTbl = Mid(strSQL, i, j - i

   strSQL = adhReplace(strSQL, cboMyTbl, strLastTbl)

   qdf.SQL = strSQL
   qdf.Close

End Sub

The strLastTbl stuff (using i and j) is clunky, but prevents you from having to store the name of the last table you used. You must have a reference to DAO set (this code is for A2K; take the "DAO." out of the dim statement if it's for 97). It uses MS Access Developer's Handbook procedure I've copied below, very useful to have around...

Public Function adhReplace(ByVal varValue As Variant, _
                           ByVal strFind As String, _
                           ByVal strReplace As String) As Variant

    
    Dim intLenFind As Integer
    Dim intLenReplace As Integer
    Dim intPos As Integer
    
    If IsNull(varValue) Then
        adhReplace = Null
    Else
        intLenFind = Len(strFind)
        intLenReplace = Len(strReplace)
        
        intPos = 1
        Do
            intPos = InStr(intPos, varValue, strFind)
            If intPos > 0 Then
                varValue = Left(varValue, intPos - 1) & strReplace & Mid(varValue, intPos + intLenFind)
                intPos = intPos + intLenReplace
            End If
        Loop Until intPos = 0
    End If
    
    adhReplace = varValue
    
End Function

 

[THoey]

You know, maybe a better way of doing this is to just have one table. Add another field to distinguish between the data you would put into table #1 and table #2 (etc...). Then, you would be accessing the same table everytime, but the difference between the datasets would just be an additional parameter to your query...

Why make things difficult? Terry M. Hoey