Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2013
    Posts
    14

    Post Automated procedure to rename table field names - regardless of actual name of fields

    Hi everybody

    This is my 1st post to DBForums as I really got acquainted with Access (2003) 2 weeks ago!
    FYI, I writing in VBA but not yet familiar enough with the recordset/DAO/etc. concepts - I think that's where my shortcomings come from, but I'm working on that.

    So here is my problem :

    MY OBJECTIVE
    I created a button that imports data from one given XLS file (SAP.xls) to an existing table ([Actual SAP for import]). This table [Actual SAP for import] will in turn populate [Actual SAP]. The XLS source file isn't supposed to change in the future.
    The source XLS and the destination table both have 16 columns and contain the same information. I just want the Access destination table to be populated with the XLS records.
    Here is what I'm planning to do it in 2 steps:

    SAP.xls -> (import to) [Actual SAP for import] -> [Actual SAP]

    Note: [Actual SAP] already has relationships with other Access objects.

    PROBLEM: theproblem is in the second step: field names are (sometimes)(slightly) different!

    BORING SOLUTION
    I know I could manually change the 16 fields names one by one.
    However, I want to make the UI as simple as possible since the DB could be used by other people (not at the same time though).
    I would like to avoid all manual updates.

    QUESTION
    Is there any way I could insert the records from source to destination regardless of the source table fields names?
    (1) force Access to modify a table fields name regardless of the source table field names, OR
    (2) somehow insert the source records to the destination table (docmd****nSQL + INSERT ?) by using variables as field names (something like CurrentDb.TableDefs(0).Fields(1).name ...)

    CODE

    Here is what I have come up with now. I have found no solution yet for the field name error I'm getting when running the SQL query.

    Code:
    Private Sub cmdimportSAP_Click()
    
    '0. Import external .xls file into MS ACCESS and name the new table 'Actual SAP for import'
    On Error GoTo ErrorHandler
    Dim strtable As String
    Dim strWorksheetPath As String
    
    strWorksheetPath = "C:\SAP.xls"
    strtable = "Actual SAP for import"
    
    DoCmd.TransferSpreadsheet transfertype:=acImport, spreadsheettype:=acSpreadsheetTypeExcel9, tablename:=strtable, filename:=strWorksheetPath, hasfieldnames:=True
    
    ErrorHandlerExit:
    Exit Sub
    
    ErrorHandler:
    MsgBox "Error number:" & Err.Number & "; Decription : " & Err.Description
    Resume ErrorHandlerExit
    
    '1. Check if both tables have the same number of fields 
    Dim tbl1 As Long
    Dim tbl2 As Long
    tbl1 = CurrentDb.TableDefs("Actual SAP for import").Fields.Count
    tbl2 = CurrentDb.TableDefs("Actual SAP").Fields.Count
    
    If tbl1 = tbl2 Then
    MsgBox "The 2 tables are compatible (same number of fields)."
    Else
    MsgBox "The 2 tables do not have the same number of fields. Please review your *.xls table and re-submit."
    
    End If
    
    '2. Change the table fields names to the matching table fields names
    '???? I HAVE NO IDEA HOW TO DO THIS 
    
    '3. Delete all 'Actual SAP' rows
    DoCmd****nSQL "DELETE [Actual SAP].[WBS Element] FROM [Actual SAP]"
    
    '4. Append all 'Actual SAP for import' rows to 'Actual SAP' table DoCmd****nSQL "INSERT INTO [Actual SAP] ( Code, Category, [WBS Element], [Document Number], [Value TranCurr], [Transaction Currency], [Valin repcur], [Name of offsetting account], [Posting Date], Name, Period, [Fiscal Year], [Vendor Name], [Purchasing Document], [Document Header Text], [Purchase order text] ) SELECT [Actual SAP for import].[1], [Actual SAP for import].[2], [Actual SAP for import].[3], [Actual SAP for import].[4], [Actual SAP for import].[5], [Actual SAP for import].[6], [Actual SAP for import].[7], [Actual SAP for import].[8], [Actual SAP for import].[9], [Actual SAP for import].[10], [Actual SAP for import].[11], [Actual SAP for import].[12], [Actual SAP for import].[13], [Actual SAP for import].[14], [Actual SAP for import].[15], [Actual SAP for import].[16] FROM [Actual SAP for import] "


    Thanks a lot for viewing my post, it took the most patient of you guys to bear with me until the end!
    I could use some help

    Cheers,


    Tarik

  2. #2
    Join Date
    Mar 2009
    Posts
    5,289
    Not sure to understand what your problem can be, however you can always use an INSERT query:
    Code:
    INSERT INTO TableDestination (Column1, Column2, Column3, ...)
    SELECT ColumnX, ColumnY, ColumnZ, ...
    FROM TableSource;
    You can even create it dynamically, using VBA:
    Code:
    Const c_SQL As QString = "INSERT INTO TableDestination (Column1, Column2, Column3, ...) " & _
                             "SELECT ColumnX, ColumnY, ColumnZ, ... " & _
                             "FROM TableSource;"
    CurrentDb.Execute c_SQL, dbFailOnError
    Have a nice day!

  3. #3
    Join Date
    Feb 2013
    Posts
    14
    Thanks for you answer Sinndho.

    Sorry if the question was somehow confusing, but I think you got it right.

    In the solutionyou provided, do (Column1, Column2, Column3, ...) automatically refer to the actual source table field names, regardless of their actual names (which could be ID, Client ID, SAP amount, etc.) ? Meaning that ACCCESS will read the actual column names instead of (Column1, Column2, Column3, ...) ?

    Basically what I want to achieve is a successful table INSERT into the destination table regardless even if the two tables field names aren't the same...

    Thanks again for the support ;-)

    Tarik

  4. #4
    Join Date
    Mar 2009
    Posts
    5,289
    Quote Originally Posted by Tarik C View Post
    In the solutionyou provided, do (Column1, Column2, Column3, ...) automatically refer to the actual source table field names, regardless of their actual names (which could be ID, Client ID, SAP amount, etc.) ? Meaning that ACCCESS will read the actual column names instead of (Column1, Column2, Column3, ...) ?
    Unfortunately not so!

    Now that I better understand the problem, here are several solutions that can work:

    1. If the number of columns is the same in both tables, you can use a "pure" SQL solution, which is the fastest. It's the procedure named SQL_Copy hereafter.

    2. If the number of rows (lines) to be imported is not very high or if the time needed for importing is no concern, you can try the "full" DAO.Recordset approach, which is the slowest, but it can handle tables with different number of columns (i.e. the number of columns in the source table is not the same as the number of columns in the destination table). It's the procedure named DAO_Copy hereafter. It uses the Function GetMaxCol().

    3. A mixed solution which is faster than the "full DAO" but slower than the "pure SQL" uses DAO objects to dynamically build an INSERT query. It's named DynSQL_Copy hereafter. It also can handle tables with different number of columns and also uses the Function GetMaxCol().
    Code:
    Option Compare Database
    Option Explicit
    
    #Const CheckTime = True
    
    #If CheckTime Then
        Private Declare Function GetTickCount Lib "kernel32" () As Long
    #End If
    
    Function GetMaxCol(ByVal SourceTableName As String, ByVal DestinationTableName As String) As Long
    
        Dim rstSrc As DAO.Recordset
        Dim rstDst As DAO.Recordset
        
        Set rstSrc = CurrentDb.OpenRecordset(SourceTableName, dbOpenSnapshot)
        Set rstDst = CurrentDb.OpenRecordset(DestinationTableName, dbOpenDynaset)
        GetMaxCol = rstSrc.Fields.Count
        If GetMaxCol > rstDst.Fields.Count Then GetMaxCol = rstDst.Fields.Count
        GetMaxCol = GetMaxCol - 1
        rstSrc.Close
        rstDst.Close
        Set rstSrc = Nothing
        Set rstDst = Nothing
        
    End Function
    
    Sub DAO_Copy(ByVal SourceTableName As String, ByVal DestinationTableName As String)
    
        Dim rstSrc As DAO.Recordset
        Dim rstDst As DAO.Recordset
        Dim lngMaxCol As Long
        Dim i As Long
        
    #If CheckTime Then
        Dim lngTStart As Long
        lngTStart = GetTickCount
    #End If
        
        lngMaxCol = GetMaxCol(SourceTableName, DestinationTableName)
        Set rstSrc = CurrentDb.OpenRecordset(SourceTableName, dbOpenSnapshot)
        Set rstDst = CurrentDb.OpenRecordset(DestinationTableName, dbOpenDynaset)
        With rstSrc
            Do Until .EOF
                rstDst.AddNew
                For i = 0 To lngMaxCol
                    rstDst.Fields(i).Value = .Fields(i).Value
                Next i
                rstDst.Update
                .MoveNext
            Loop
            .Close
        End With
        rstDst.Close
        Set rstSrc = Nothing
        Set rstDst = Nothing
        
    #If CheckTime Then
        Debug.Print GetTickCount - lngTStart
    #End If
    
    End Sub
    
    Sub DynSQL_Copy(ByVal SourceTableName As String, ByVal DestinationTableName As String)
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        Dim strBuffer As String
        Dim strSQL As String
        Dim lngMaxCol As Long
        Dim i As Long
        
        
    #If CheckTime Then
        Dim lngTStart As Long
        lngTStart = GetTickCount
    #End If
        
        lngMaxCol = GetMaxCol(SourceTableName, DestinationTableName)
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs(DestinationTableName)
        For i = 0 To lngMaxCol
            If Len(strBuffer) > 0 Then strBuffer = strBuffer & ", "
            strBuffer = strBuffer & tdf.Fields(i).Name
        Next i
        strSQL = "INSERT INTO " & DestinationTableName & "( " & strBuffer & " ) "
        strBuffer = ""
        Set tdf = dbs.TableDefs(SourceTableName)
        For i = 0 To lngMaxCol
            If Len(strBuffer) > 0 Then strBuffer = strBuffer & ", "
            strBuffer = strBuffer & tdf.Fields(i).Name
        Next i
        strSQL = strSQL & "SELECT " & strBuffer & " FROM " & SourceTableName
        CurrentDb.Execute strSQL, dbFailOnError
        Set tdf = Nothing
        Set dbs = Nothing
        
    #If CheckTime Then
        Debug.Print GetTickCount - lngTStart
    #End If
    
    End Sub
    
    Sub SQL_Copy(ByVal SourceTableName As String, ByVal DestinationTableName As String)
    
        Const c_SQL As String = "INSERT INTO @D SELECT * FROM @S;"
        
    #If CheckTime Then
        Dim lngTStart As Long
        lngTStart = GetTickCount
    #End If
        
        CurrentDb.Execute Replace(Replace(c_SQL, "@D", DestinationTableName), "@S", SourceTableName), dbFailOnError
        
    #If CheckTime Then
        Debug.Print GetTickCount - lngTStart
    #End If
    
    End Sub
    I have included compiler directives into the code, so that the procedures print their execution time (in milliseconds) in the Immediate Window. On my test machine (not a vary fast one) running Windows XP (SP3) and Office 2003 (SP3), here are the results for importing a table of 18 columns and 15.000 rows:
    Code:
    SQL_Copy "Tbl_Source", "Tbl_Destination"
     62 
    DynSQL_Copy "Tbl_Source", "Tbl_Destination"
     74 
    DAO_Copy "Tbl_Source", "Tbl_Destination"
     484
    Have a nice day!

  5. #5
    Join Date
    Feb 2013
    Posts
    14
    Thank you SO much Sinndhoo. Your answer was exhaustive and you really took me through the reasoning. I find the DynSQL suggestion very helpful.

    I have looked into your code and opted for the Dynamic SQL solution. The other SQL_Copy solution would only work if the field names are the same in the [Source Table] and the [Destination table] (I then bump into Error 3127).

    However there is a new issue with DynSQL: I am getting a 3162 error message ("You tried to assign the Null value to a variable that is not a variant data type.").
    I am surprised to see this message when I am executing the strSQL.
    cf. The error happens here :
    CurrentDb.Execute strSQL, dbFailOnError

    [Note: The destination table only has an Auto Number Primary Key (first field, called "ID"). All the other fields are not required.]

    However, when I run the same manual SQL Append Query, I do not have any problem:

    INSERT INTO [Destination Table] ( [Field 1], [Field 2], [Field 3], [Field 4])
    SELECT [Source Table].[Field A], [Source Table].[Field B], [Source Table].[Field C], [Source Table].[Field D] FROM [Source Table];
    Last edited by Tarik C; 02-12-13 at 23:18.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,289
    This is possible if there is a Null or empty (zero-lengh string) value in the first field of a row in SourceTableName. If the first column of the destination table is defined as an AutoNumber type (which forbids Null values), data should not be imported in this column. This can be easily done. Replace:
    Code:
    For i = 0 To lngMaxCol
    with:
    Code:
    For i = 1 To lngMaxCol
    In more complex situations, you should have to parse each row from the source and reject those that are not compatible with the data definition of the destination. That's what the import wizards does when "bad" lines are stored in an "ImportError" table.
    Have a nice day!

  7. #7
    Join Date
    Feb 2013
    Posts
    14
    Yeah I thought of that too. I don't think that the error comes from there.

    The Destination Table auto-numbered field is't targetted by the INSERT INTO.
    I was actually hoping to insert rows of records while the Auto-Number would be automatically created.

    So we have something like:

    [Source Table].[Field 1] ---> INSERT INTO [Destination Table].[Field A]
    [Source Table].[Field 2] ---> INSERT INTO [Destination Table].[Field B]
    [Source Table].[Field 3] ---> INSERT INTO [Destination Table].[Field C]
    [Source Table].[Field 4] ---> INSERT INTO [Destination Table].[Field D]
    .... and the Auto-Numbered [Destination Table].[Field X] isn't part of the INSERT SQL.

    Some of the 10,000 records I'm inserting have Null Values for some fields only, some not at all.

    The dynamic SQL Sub that you proposed should work fine, if it wasn't for this error message I'm getting. :-(

    Thanks again for your patience,

    Tarik

  8. #8
    Join Date
    Mar 2009
    Posts
    5,289
    Can you please post the actual SQL expression, as it is before assigning it to the SQL property of the query?
    Have a nice day!

  9. #9
    Join Date
    Feb 2013
    Posts
    14
    Hi Sinndho,

    You were completely right. As you advised I modified the loop so that to avoid INSERTing INTO the first (auto-numbered) column. That actually solved the problem of the Error 3162.
    - I actually found a way to look into the dynamic SQL generated by the code and I realized that by comparing with the one that worked. -

    My problem is solved. THANKS a lot for taking me through it.

    Tarik

    PS: Any way I can upvote your solution or thank you for it through the DB forum? Just let me know..

  10. #10
    Join Date
    Mar 2009
    Posts
    5,289
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •