Help editing VBA for Access

[AmandaF15]

So I was wondering if someone here could help me edit some VBA code for access.

Here is the code:

Public Function GTAS()
 
   Dim SBRLink2017 As DAO.Database
   Set SBRLink2017 = CurrentDb
  
   Dim delSQL As String
   Dim updSQL As String
   'Dim LinSQL As String
  
   DoCmd.SetWarnings False
  
   delSQL = "DELETE tbl_GTAS.* FROM tbl_GTAS';"
  
   DoCmd.RunSQL (delSQL)
  
Dim tdf As DAO.TableDef
 
Set db = CurrentDb
 
For Each tdf In db.TableDefs
 
    ' ignore system and temporary tables
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
 
        Dim sTable As String
        sTable = tdf.Name
 
        Dim strSQL
        strSQL = "INSERT INTO Tbl_GTAS ( SF133_Rpt_Line, LineDescription, LineAmt, TS)" & _
            " SELECT T.F1, T.F2, T.F3, '" [B]& Replace(sTable, "[_NEW SF 133]", "") & "' AS TS "[/B] & _
            "FROM [" & sTable & "] AS T " & _
            "GROUP BY T.F1, T.F2, T.F3,'" & Replace(sTable, "[_NEW SF 133]", "") & "';"
 
Debug.Print strSQL
        'do what you will with SQL
        DoCmd.RunSQL strSQL
    End If
 
Next
 
   updSQL = "UPDATE Tbl_GTAS SET Tbl_GTAS.TS_SF133_Rpt_Line = [TS] & '_' & [SF133_Rpt_Line];"
  
   DoCmd.RunSQL (updSQL)
  
'LinSQL = "DELETE Tbl_GTAS.LineAmt FROM Tbl_GTAS WHERE Tbl_GTAS.LineAmt Is Null OR Tbl_GTAS.LineAmt<0.001 And Tbl_GTAS.LineAmt>-0.001';"
  
   'DoCmd.RunSQL (LinSQL)
  
   DoCmd.SetWarnings True
   MsgBox ("The procedure is complete")
  
 End Function

I am specifically having trouble with this in the code: & Replace(sTable, "[_NEW SF 133]", "") & "' AS TS "


It is returning my TS like this:
Excel 2012

	A	B	C	D	E
4	TS	SF133_Rpt_Line	TS_SF133_Rpt_Line	LineAmt	LineDescription
5	75-1012-0943_NEW SF 133	1000	75-1012-0943_NEW SF 133_1000	$1,000,000.00	Description
6	75-1012-0943_NEW SF 133	1021	75-1012-0943_NEW SF 133_1021	$1,000,000.00	Description

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Data​

When I really need it to show up like this: (without the _NEW SF 133)
Excel 2012

	A	B	C	D	E
4	TS	SF133_Rpt_Line	TS_SF133_Rpt_Line	LineAmt	LineDescription
5	75-1012-0943	1000	75-1012-0943_1000	$1,000,000.00	Description
6	75-1012-0943	1021	75-1012-0943_1021	$1,000,000.00	Description

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Data​

Can anyone help me edit the replace function with something that will give me the TS only?

Thanks,
Amanda

 

[xenou]

Well, I think that's already too far ahead. We need to take your sql and make it good sql, first. Then we can make it into VBA. Do you have a working SQL query?

 

[AmandaF15]

I have the sql querys for each individual table but I do not have one that reads all the tables other then the one in my original post.

This is one for the individual table.

[FONT="Calibri"][COLOR=#000000]INSERT INTO Tbl_GTAS ( SF133_Rpt_Line, LineDescription, LineAmt, TS ) 
SELECT [75-1012-0943_NEW SF 133].F1, [75-1012-0943_NEW SF 133].F2, [75-1012-0943_NEW SF 133].F3, '75-1012-0943' AS TS 
FROM [75-1012-0943_NEW SF 133] 
GROUP BY [75-1012-0943_NEW SF 133].F1, [75-1012-0943_NEW SF 133].F2, [75-1012-0943_NEW SF 133].F3, '75-1012-0943'[/COLOR][/FONT]

 

[xenou]

OKay, run this and report result so I can see what kind of tables names we are actually working with:

Public Sub GTAS()
 
Dim tdf As DAO.TableDef
Dim i As Long
Dim sTable As String

For Each tdf In CurrentDb.TableDefs
 
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
        
        i = i + 1
        sTable = tdf.Name
        Debug.Print Format(i, "000") & " " & sTable

    End If

Next
 
  
End Sub