Hi VP,
here is some food for thought...
This was set up for reading from Excel. All files in a
particular directory would be imported at the same time.
Susbsequent imports would be from another directory -- hence
tying files to directories...
as structured, is not a multi-user application
Here are some tables I used ("usys" is user system and hides
just like the "msys" objects -- show/hide system objects
from Tools, Options, View tab):
*usys_Flds* alias Fld
FldID, autonumber
Fld, text
DatType,
IsKey, yes/no
*usys_MapDefinitions* alias Map
MapID, autonumber
ImportFld, text
FldID, long
Ignore, yes/no
Flag, yes/no
*usys_MapCurrent* alias MapCur
SheetID, long
MapCurID, autonumber
FldNum, integer
ImportFld, text
MapID, long
*usys_Dirs* alias D
DirID, autonumber
DirPath, text, 255
ImportDate, date
*usys_files* alias F
DirID, long
FileID, autonumber
FilName, text
KeyValue1
KeyValue2
KeyValue3
*usys_Sheets* alias Sht
FileID, long
SheetID, autonumber
ShtNum, integer
ShtName, text
TblName, text
RecsAdd, long
RecsUp, long
IsMissingKeys, yes/no
Ignore, yes/no
Flag, yes/no
and the, of course, your data tables...
form: Import_Mapping
RecordSource -->
SELECT Map.*, Fld.Fld AS TargetFld, Fld.strDatType
FROM usys_Flds AS Fld RIGHT JOIN usys_MapDefinitions AS Map
ON Fld.FldID = Map.FldID
WHERE (((Map.Flag)=True) AND ((Map.ImportFld)<>"StudID"))
ORDER BY Map.FldNum;
StudID is the key field that is added to the imported data
before transfer is done
-- this is a continuous form to show user what matched up
with definitions and allow them to change mappings
form: usys_dummy
used to swap SourceObject for Import_Mapping when underlying
data is being manipulated (it is not visible on the form either)
form: ImportData
browse button to choose directory
listbox shows files
listbox shows sheets in each file -- user can pick or get
them all for each file
once Browse is chosen, the program code populates the usys
tables and shows the mapping subform
subform for Import_Mapping/usys_dummy
there are buttons to edit the source data and to perform the
data transfer
once the data is imported, the key field is added to the
import data along with some other tracking fields
'~~~~~~~~~~~~~~~~
Sub testaddFieldToTable()
AddFieldToTable "test", "AutoID", dbLong, , "*AN*"
AddFieldToTable "test", "SomeID", dbLong, , "*Null*"
AddFieldToTable "test", "ImportLog", dbText, 255
AddFieldToTable "test", "DateCreated", dbDate, , "*Now*"
End Sub
'~~~~~~~~~~~~~~~~
'this goes into a general module
Function AddFieldToTable( _
pTablename As String, _
pFldname As String, _
pDataType As Integer, _
Optional pFieldSize As Integer, _
Optional pOptions As String) _
As Boolean
'written by Crystal
'strive4peace2007 at yahoo.com
'PARAMETERS
'pTablename --> name of table to modify structure of
'pFldname --> name of field to create
'pDataType --> dbText, dbLong, dbDate, etc
'pFieldSize --> length for text fields
'pOptions --> *AN* = autonumber
' --> *Null* --> DefaultValue = Null
' --> *Now* --> DefaultValue = Now()
'NEEDS Reference to
'a Microsoft DAO Library
On Error GoTo AddFieldToTable_error
Dim db As Database, Fld As Field
'you could make this a passed parameter
' and open another database
Set db = CurrentDb
With db.TableDefs(pTablename)
Select Case pDataType
Case dbText
'Text
Set Fld = .CreateField(pFldname, _
pDataType, pFieldSize)
Case Else
'Long Integer, Date, etc
Set Fld = .CreateField(pFldname, pDataType)
End Select
If InStr(pOptions, "*AN*") > 0 Then
'Autonumber
Fld.Attributes = dbAutoIncrField
End If
If InStr(pOptions, "*Null*") > 0 Then
'Null for DefaultValue
Fld.DefaultValue = "Null"
End If
If InStr(pOptions, "*Now*") > 0 Then
'Now for DefaultValue
Fld.DefaultValue = "=Now()"
End If
.Fields.Append Fld
End With
db.TableDefs.Refresh
DoEvents
' MsgBox "Added --> " & pFldname _
& " to --> " & pTablename, , "Done"
AddFieldToTable_exit:
On Error Resume Next
Set Fld = Nothing
Set db = Nothing
Exit Function
AddFieldToTable_error:
'if the field is already there, ignore error
If Err = 3191 Then Resume Next
MsgBox Err.Description, , _
"ERROR " & Err.Number & " AddFieldToTable"
'press F8 to step through code and fix problem
'comment next line after debugged
Stop: Resume
Resume AddFieldToTable_exit
End Function
'~~~~~~~~~~~~~~~~
you will need to rely heavily on constructing SQL in your code.
http://www.microsoft.com/office/com...orms&tid=03dd204d-5762-4529-a321-fb440892ce71
read the post that has "Don't let the SQL acronym intimidate
you, it is not tough to learn the basics. " at the beginning
Anyway, since all queries are SQL statements, becoming
familar with SQL will carry you a long way...
~~~~~
If you drop me an email (addr in my siggy), I will send you
a ppt presentation with some screen shots and program flow
-- make sure the subject line explains what you want. I am
sorry, I cannot share the code.
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
, I'm stuggling with it right now. Can you share your code
good luck