Getting Excel To Access

[Guest]

How do I transmiited data fromExcel to Acces to match the name,date and
record id. The useres spreadsheet is all in columnn A, The spreadsheet file
will be named the same as the fileds entered on the Access table.
Example Requestor Name: Charles Right.tblTapeTracking
dtedateRequest:04/12/05:tblTapeTracking.
lngrequestId: autonumbered on the tblTapeTracking

The Excel spreadsheet file will be named : Charles Right 04/12/05
All the data is in column A, there are 100 rows filled in. This data will
the go to the tblTapetable. The fields are lngRequestID set as number and
lngVolser.

I need to ensure that the Excel data matches Access record,because this info
will be displayed in a list box ln a form and on a report.

I don't know any other way to ask the question to get the results I need.
This is my second attempt.

 

[Rob Oldfield]

Can I just check what it is you're after...

You have a table called tblTapeTracking which contains fields called
'Requestor Name' and 'dtedateRequest'. That is going to provide a unique
reference to a file which contains a single column of data which you need to
place into another table called tblTapeTable. There is a one to many
relationship between these tables based on the field lngRequestID.

Is that right?

Does the data need to be permanently imported into the database, or would a
temporary link to the spreadsheet data while it's in use be better?

 

[Guest]

The answer is yes and data needs to remain in the database for each requestor.

 

[Rob Oldfield]

OK. You'll need to decide what event actually triggers the import, whether
there's a button a user presses, or it checks when the database opens, or
you want only do it when the record is displayed in a form or report... but
the idea will be similar to the code below. I'd probably add a Yes/No field
onto the main table (called Imported below) to record which records you have
already imported the data for - avoiding the possiblity of the same data
being imported twice.

This is assuming you want to click a button and import all available data...

dim db as database
dim rs as recordset
set db=currentdb

'RootPath is the path to the folder containing the files
dim rootpath as string
rootpath="c:\whatever\"

'Grab the data, ignoring anything already imported
set rs=db.openrecordset("select * from tblTapeTracking where not imported")

'Variables for later
dim fullpath as string, sql as string

'Loop through the recordset
with rs
do while not .eof
'Build a string of the path to check
fullpath=rootpath & ![Requestor Name] & ![dtedateRequest] &".xls"

'Check whether the file exists
if len(dir(fullpath))>0 then
'Create a temporary link to the sheet
docmd.transferspreadsheet acLink, , "tmpImport", fullpath, False

'Build the sql to append the data to your table - see notes
later
sql="INSERT INTO tblTapeTable ( FieldName, lngRequestID) "& _
"SELECT tmpImport.F1, "&cstr(!lngRequestID)& _
"FROM tmpImport;"

'and run the query
db.execute sql

'Lose the temporary linked table
docmd.deleteobject actable,"tmpImport"

'Update the Imported field to show that this record is done
.edit
!Imported=true
.update
'Move to the next record
.movenext
endif
loop
end

'Tidy up
rs.close
set db=nothing

...and that's it. Though I've probably got something wrong in there. A
couple of things that you'll need to check... I'm assuming that the
spreadsheet won't contain a header row - hence the False on the
transferspreadsheet line and the assumption that the field in tmpImport will
be called F1. I also don't know what field it is that you want to populate
in tblTapeTable - I've called that just FieldName. You'll also need some
kind of error checking on the SQL execution e.g. for when users enter
incorrect data.

Good luck!

 

[Guest]

Thank You Rob

OK. You'll need to decide what event actually triggers the import, whether
there's a button a user presses, or it checks when the database opens, or
you want only do it when the record is displayed in a form or report... but
the idea will be similar to the code below. I'd probably add a Yes/No field
onto the main table (called Imported below) to record which records you have
already imported the data for - avoiding the possiblity of the same data
being imported twice.

This is assuming you want to click a button and import all available data...

dim db as database
dim rs as recordset
set db=currentdb

'RootPath is the path to the folder containing the files
dim rootpath as string
rootpath="c:\whatever\"

'Grab the data, ignoring anything already imported
set rs=db.openrecordset("select * from tblTapeTracking where not imported")

'Variables for later
dim fullpath as string, sql as string

'Loop through the recordset
with rs
do while not .eof
'Build a string of the path to check
fullpath=rootpath & ![Requestor Name] & ![dtedateRequest] &".xls"

'Check whether the file exists
if len(dir(fullpath))>0 then
'Create a temporary link to the sheet
docmd.transferspreadsheet acLink, , "tmpImport", fullpath, False

'Build the sql to append the data to your table - see notes
later
sql="INSERT INTO tblTapeTable ( FieldName, lngRequestID) "& _
"SELECT tmpImport.F1, "&cstr(!lngRequestID)& _
"FROM tmpImport;"

'and run the query
db.execute sql

'Lose the temporary linked table
docmd.deleteobject actable,"tmpImport"

'Update the Imported field to show that this record is done
.edit
!Imported=true
.update
'Move to the next record
.movenext
endif
loop
end

'Tidy up
rs.close
set db=nothing

...and that's it. Though I've probably got something wrong in there. A
couple of things that you'll need to check... I'm assuming that the
spreadsheet won't contain a header row - hence the False on the
transferspreadsheet line and the assumption that the field in tmpImport will
be called F1. I also don't know what field it is that you want to populate
in tblTapeTable - I've called that just FieldName. You'll also need some
kind of error checking on the SQL execution e.g. for when users enter
incorrect data.

Good luck!

The answer is yes and data needs to remain in the database for each requestor.