If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Automated procedure to rename table field names - regardless of actual name of fields

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,123
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!
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,123
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!
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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 22:18.
Reply With Quote
  #6 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,123
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!
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,123
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!
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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..
Reply With Quote
  #10 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,123
You're welcome!
__________________
Have a nice day!
Reply With Quote
Reply

Tags
access, fieldname, import

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On