File Import

[Jamie Longmire]

Hi everyone,
I want to import some data from an external source into a table but only if
it has not already been imported. Here is the structure

TableName "MobilePhoneData"
ID - Primary Key
CallDetails
BillRegisterID

TableName - "BillRegister"
ID - Primary Key
IssueDate
FileName

BillRegister.ID and MobilePhoneData.BillRegisterID are joined via a One to
Many relationship.

I want the import routine to verify if the filename selected to be imported
already exists in BillRegister. If it does it will not import the data into
the MobilePhoneData table. If the filename does not exist then it will
create a new line item in BillRegister with the issue date and filename and
import the data into MobilePhoneData with the BillRegister.ID value being
place in the MobilePhoneData.BillRegisterID field.

Any pointer are much appreciated.
Regards,
Jamie

 

[John Nurick]

Hi Jamie,

This fragment of air code creates the BillRegister record and retrieves
its ID ready for use. It assumes that you provide a subroutine that can
then create the records in the other table: declaration at the end.

....
Dim strFolder As String
Dim strFileToImport As String
Dim strSQL As String
Dim strIssueDate As String
Dim lngID As Long

strFolder= "D:\Folder\"
strFileToImport = "File999.txt" 'or whatever

If DCount("ID", "BillRegister", _
"FileName='" & strFileToImport) = 0 Then 'not yet imported

'Add record to BillRegister. NB: ASSUMES ID IS AUTONUMBER
strIssueDate = "#" & Format(Date(), "mm/dd/yyyy") & "#"
strSQL = "INSERT INTO BillRegister (IssueDate, FileName) " _
& "VALUES (" & strIssueDate & ", '" & strFileToImport & "');"
dbEngine(0).Workspaces(0).Execute strSQL

'Get ID of new record
lngID = DLookup("ID", "BillRegister", _
"IssueDate=" & strIssueDate _
& " AND FileName='" & strFileName & "'")
ImportCallDetails strFolder & strFileName, lngID
End If
....


Private Sub ImportCallDetails(FileSpec As String, _
BillRegisterID As Long)
Dim strSQL As String

If Len(Dir(FileSpec)) = 0 Then
'File does not exist: handle error
Else
'Extract data from external source and append to table
strSQL = "INSERT INTO MobilePhoneData SELECT " _
& BillRegisterID & " AS BillRegisterID, CallDetails " _
& " FROM " & [External Source] & ";"
DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError
End Sub

[External Source] might look something like these:
[Text;HDR=No;Database=C:\MyFolder\;].MyFile#txt
[Excel 8.0;HDR=Yes;Database=C:\MyBook.xls].Sheet1