L
L Gaines
I am importing data from one Access 2002 .mdb file to another by way of some
VBA code which extracts the data from the external tables and inserts it into
tables that are part of an application which uses different table and field
names. I did not write the original application code but am trying to
upgrade it to add some field data that is new in the external application.
The two data sets are matched to eachother through use of a table
"tblDataDictionary" during the import process. In my initial attempts to
convert the tblDataDictionary definitions it appeared that most of the data
had been importing as expected. On closer inspection there are two new
boolean fields which seem to convert all of their records to "No" during the
import process. I have looked at both the external table and the table I am
bringing the data into and ensured that the data types are both the same
(They are both text data types now, although I had originally defined the two
feilds in question as Yes/No type in the application table). I checked and
rechecked the export and import field names in my tblDataDictionary table to
be sure that the syntax was exactly as it appears in the tables. I also
checked to be sure that the table names in tblDataDictionary had correct
syntax. The data in the table I am exporting from is experssed as Y and N
in the individula records. I even looked at the visual basic script that
controls the import process and I can't find anything that looks wrong (a
copy of the code follows).
Can anyone tell me why the content of my Yes/No fields is not importing
correctly?
Thanks for your help.
L. Gaines
********************
Public Sub Load_Section1()
'Organization Information
Dim strSQL As String
Dim txtImportDatabaseName As String
Dim txtImportTableName As String
Dim txtAccessTableName As String
Dim txtAccessTableUserName As String
Dim objRS As DAO.Recordset
Dim objDBS As DAO.Database
Dim txtComma As String
Dim intTotalRecs As Integer
Dim intRecordCounter As Integer
'''''''''''''''''''''''''''''''''''''''''''''''''''
'Section 1
txtImportTableName = "Section_1"
txtAccessTableName = "tblOrganization_Information"
On Error Resume Next ' OK if table does not exist for deletion
DoCmd.DeleteObject acTable, txtImportTableName
DoCmd.TransferDatabase acImport, "Microsoft Access", strImportFileName,
acTable, txtImportTableName, txtImportTableName
On Error GoTo Error_Branch
Set objDBS = CurrentDb
'Get Access Table User Name
strSQL = "SELECT AccessTableUserName" & Space(1)
strSQL = strSQL & "FROM tblDataDictionary_Tables" & Space(1)
strSQL = strSQL & "WHERE AccessTableName='" & txtAccessTableName & "'"
Set objRS = objDBS.OpenRecordset(strSQL)
If objRS.EOF Then
GoTo Error_Branch
End If
objRS.MoveFirst
txtAccessTableUserName = objRS!AccessTableuserName
'Empty Access table
strSQL = "DELETE * FROM" & Space(1) & txtAccessTableName
objDBS.Execute (strSQL)
'Build Insert statement for this section using data dictionary
strSQL = "SELECT AccessFieldName, ImportFieldName, WebTableName" & Space(1)
strSQL = strSQL & "FROM tblDataDictionary" & Space(1)
strSQL = strSQL & "WHERE WebTableName='" & txtImportTableName & "'"
Set objRS = objDBS.OpenRecordset(strSQL)
If objRS.EOF Then
GoTo Error_Branch
End If
strsql_1 = "insert into" & Space(1) & txtAccessTableName & "("
strsql_3 = ") SELECT" & Space(1)
strsql_5 = Space(1) & "FROM" & Space(1) & txtImportTableName
objRS.MoveLast
intTotalRecs = objRS.RecordCount
objRS.MoveFirst
Do While Not objRS.EOF
intRecordCounter = intRecordCounter + 1
If intRecordCounter <> intTotalRecs Then txtComma = "," Else txtComma = ""
strsql_2 = strsql_2 & objRS!AccessFieldName & txtComma
strsql_4 = strsql_4 & objRS!ImportFieldName & txtComma
objRS.MoveNext
Loop
strSQL = strsql_1 & strsql_2 & strsql_3 & strsql_4 & strsql_5
DoCmd.RunSQL (strSQL)
'Delete import table from this database
DoCmd.DeleteObject acTable, txtImportTableName
GoTo Exit_Branch
Error_Branch:
MsgBox "An import error occurred while loading the " &
txtAccessTableUserName & " table." & Chr(10) & Chr(10) & "Please contact
system administrator.", vbExclamation, "Import Error"
Import_Error = True
Exit Sub
Exit_Branch:
End Sub
VBA code which extracts the data from the external tables and inserts it into
tables that are part of an application which uses different table and field
names. I did not write the original application code but am trying to
upgrade it to add some field data that is new in the external application.
The two data sets are matched to eachother through use of a table
"tblDataDictionary" during the import process. In my initial attempts to
convert the tblDataDictionary definitions it appeared that most of the data
had been importing as expected. On closer inspection there are two new
boolean fields which seem to convert all of their records to "No" during the
import process. I have looked at both the external table and the table I am
bringing the data into and ensured that the data types are both the same
(They are both text data types now, although I had originally defined the two
feilds in question as Yes/No type in the application table). I checked and
rechecked the export and import field names in my tblDataDictionary table to
be sure that the syntax was exactly as it appears in the tables. I also
checked to be sure that the table names in tblDataDictionary had correct
syntax. The data in the table I am exporting from is experssed as Y and N
in the individula records. I even looked at the visual basic script that
controls the import process and I can't find anything that looks wrong (a
copy of the code follows).
Can anyone tell me why the content of my Yes/No fields is not importing
correctly?
Thanks for your help.
L. Gaines
********************
Public Sub Load_Section1()
'Organization Information
Dim strSQL As String
Dim txtImportDatabaseName As String
Dim txtImportTableName As String
Dim txtAccessTableName As String
Dim txtAccessTableUserName As String
Dim objRS As DAO.Recordset
Dim objDBS As DAO.Database
Dim txtComma As String
Dim intTotalRecs As Integer
Dim intRecordCounter As Integer
'''''''''''''''''''''''''''''''''''''''''''''''''''
'Section 1
txtImportTableName = "Section_1"
txtAccessTableName = "tblOrganization_Information"
On Error Resume Next ' OK if table does not exist for deletion
DoCmd.DeleteObject acTable, txtImportTableName
DoCmd.TransferDatabase acImport, "Microsoft Access", strImportFileName,
acTable, txtImportTableName, txtImportTableName
On Error GoTo Error_Branch
Set objDBS = CurrentDb
'Get Access Table User Name
strSQL = "SELECT AccessTableUserName" & Space(1)
strSQL = strSQL & "FROM tblDataDictionary_Tables" & Space(1)
strSQL = strSQL & "WHERE AccessTableName='" & txtAccessTableName & "'"
Set objRS = objDBS.OpenRecordset(strSQL)
If objRS.EOF Then
GoTo Error_Branch
End If
objRS.MoveFirst
txtAccessTableUserName = objRS!AccessTableuserName
'Empty Access table
strSQL = "DELETE * FROM" & Space(1) & txtAccessTableName
objDBS.Execute (strSQL)
'Build Insert statement for this section using data dictionary
strSQL = "SELECT AccessFieldName, ImportFieldName, WebTableName" & Space(1)
strSQL = strSQL & "FROM tblDataDictionary" & Space(1)
strSQL = strSQL & "WHERE WebTableName='" & txtImportTableName & "'"
Set objRS = objDBS.OpenRecordset(strSQL)
If objRS.EOF Then
GoTo Error_Branch
End If
strsql_1 = "insert into" & Space(1) & txtAccessTableName & "("
strsql_3 = ") SELECT" & Space(1)
strsql_5 = Space(1) & "FROM" & Space(1) & txtImportTableName
objRS.MoveLast
intTotalRecs = objRS.RecordCount
objRS.MoveFirst
Do While Not objRS.EOF
intRecordCounter = intRecordCounter + 1
If intRecordCounter <> intTotalRecs Then txtComma = "," Else txtComma = ""
strsql_2 = strsql_2 & objRS!AccessFieldName & txtComma
strsql_4 = strsql_4 & objRS!ImportFieldName & txtComma
objRS.MoveNext
Loop
strSQL = strsql_1 & strsql_2 & strsql_3 & strsql_4 & strsql_5
DoCmd.RunSQL (strSQL)
'Delete import table from this database
DoCmd.DeleteObject acTable, txtImportTableName
GoTo Exit_Branch
Error_Branch:
MsgBox "An import error occurred while loading the " &
txtAccessTableUserName & " table." & Chr(10) & Chr(10) & "Please contact
system administrator.", vbExclamation, "Import Error"
Import_Error = True
Exit Sub
Exit_Branch:
End Sub