Export from Office 2003 Excel to Access

[Steve]

I created an Excel spreadsheet as a template for employee data that multiple
supervisors rename and daily put data into concerning their employees. I
want to create a macro in the Excel template so that the supervisors can
daily export the employee data in their spreadsheet to a particular table in
a multi-user multi-purpose Access database. The data to be exported for each
day can be put into a contiguous area with the Access key field (employee ID)
as the first field and the date as the second field. The only qualification
would be to ask if you want to replace data that has the same employee ID and
date. There is always the possibility that two supervisors might be doing
the export at the same time, but they would not have the same employees.

 

[ryguy7272]

Code here is run from Access:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

These are good too:
http://www.erlandsendata.no/english/index.php?d=envbadacexportado
http://www.erlandsendata.no/english/index.php?d=envbadacexportdao

Maybe this:
http://exceltip.com/st/Export_data_from_Excel_to_Access_(ADO)_using_VBA_in_Microsoft_Excel/425.html

Good luck,
Ryan--

 

[Steve]

I will try this. The only thing that appears to be missing is if it finds a
record in the Access table with the same first and second fields (employee ID
and date) I need to describe the data in a messagebox and ask if you want to
replace it, then act on a yes or no choice and move on to the next record.
Such as "404444 already exists on 6/26/09, do you want to replace it?"

 

[ryguy7272]

Right! You could do that, or import everything; the data is automatically
appended under the last used record. Then run an update query, to delete
dupes. Check this out:
http://www.databasejournal.com/feat...lete-Duplicate-Records-From-Access-Tables.htm

http://articles.techrepublic.com.com/5100-10878_11-1043732.html

http://office.microsoft.com/en-us/access/HA010345581033.aspx

What you described would probably take a looooooong time, especially if you
are importing lots of dupes, right. Or, maybe I missed something...

Finally, you could use code such as this:
Private Sub Command1_Click()
Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rsttblPatient As ADODB.Recordset
Dim strCnn As String
'Dim mydb As DAO.Database
Dim mydb As String

'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
'On Error GoTo S:
cnn1.Open strCnn

' Open contact table.
Set rsttblPatient = New ADODB.Recordset
rsttblPatient.CursorType = adOpenKeyset
rsttblPatient.LockType = adLockOptimistic
rsttblPatient.Open "tblPatient", cnn1, , , adCmdTable


'get the new record data
rsttblPatient.Update
rsttblPatient!MR = MR
rsttblPatient!FirstName = FirstName
rsttblPatient!LastName = LastName
rsttblPatient!ConsultDate = ConsultDate

rsttblPatient.Update

' Show the newly added data.
MsgBox "New patient: " & rsttblPatient!FirstName & " " &
rsttblPatient!LastName & " has been successfully added!!"


'close connections
rsttblPatient.Close
cnn1.Close
'S: MsgBox "An error has occurred. Please close the database, reopen, and
try again."

Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub

That will automatically search for matching records:
#1) if the record is in the table, data will be UPDATED
#2) if the record is not in the table, data will be ADDED



HTH,
Ryan---

 

[Steve]

Each supervisor has 10-20 employees, and this is a once-a-day report on their
employees. The only time there could be duplicates is if a correction needed
to be made, usually only to a few employees, after the data was originally
sent, and that doesn't happen very often. No big deal. So each employee
ultimately has only one record per day. We definitely don't want to confirm
the data transfer for each one, just the completion of the task.

I will try to adapt what you sent and see how it works.