How do I import an excel file to an access database using active .

[Guest]

I have a database of golfers which has their handicaps listed, along with
other data. Every two weeks I get updated handicaps in an excel file. I am
able to update my access database offline and then upload the updated
database via Front Page.
However, I would rather use ADO and write some code which would read the
values from the excel file and the use the recordset update procedures via an
active server page. I use active server pages now to update addresses, show
reports, etc. but don't know how to do the update described above.

Thanks, Dave

 

[Guest]

There is a way to do it with odbc, but then you should be able to setup an
odbc connection and the excel provider should be available on the machine.
Probably this can also be done by defining the proper connectionstring.

You can also handle this by saving your Excel file as a data XML and this
XML can be opened with an ADO.Recordset. Still 1 step but an easier step I
guess.

- Raoul

 

[barmaley]

I have a database of golfers which has their handicaps listed, along with
other data. Every two weeks I get updated handicaps in an excel file. I am
able to update my access database offline and then upload the updated
database via Front Page.
However, I would rather use ADO and write some code which would read the
values from the excel file and the use the recordset update procedures via an
active server page. I use active server pages now to update addresses, show
reports, etc. but don't know how to do the update described above.

Thanks, Dave

Here is snipet of code I am using in my word form. Make sure you have ADO
reference in excel. Artem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sub Update_DB()
Dim objApp As Object
Dim strDocName As String
Dim cnMain As ADODB.Connection
Dim rsExtract As ADODB.Recordset

Dim strPath As String
Dim strTemp As String

Dim stDocName As String
Dim stLinkCriteria As String

strPath = CurrentProject.FullName 'db and excel file reside in same
folder

Set cnMain = New ADODB.Connection
With cnMain
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source").Value = strPath
.Open
End With

Set rsExtract = New ADODB.Recordset

With rsExtract
.ActiveConnection = cnMain
.Source = "SELECT * FROM [dbo_Table]"
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Open
End With

rsExtract.AddNew
rsExtract.Fields("CID").Value = Activecell.value 'updates field with
active cell value
rsExtract.Update

rsExtract.Close
cnMain.Close
Set rsExtract = Nothing
Set cnMain = Nothing

End Sub

 

[barmaley]

Correction: Here is snipet of code I am using in Excel to create and update
record

I have a database of golfers which has their handicaps listed, along with
other data. Every two weeks I get updated handicaps in an excel file. I am
able to update my access database offline and then upload the updated
database via Front Page.
However, I would rather use ADO and write some code which would read the
values from the excel file and the use the recordset update procedures

via

an
active server page. I use active server pages now to update addresses, show
reports, etc. but don't know how to do the update described above.

Thanks, Dave

Here is snipet of code I am using in my word form. Make sure you have ADO
reference in excel. Artem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sub Update_DB()
Dim objApp As Object
Dim strDocName As String
Dim cnMain As ADODB.Connection
Dim rsExtract As ADODB.Recordset

Dim strPath As String
Dim strTemp As String

Dim stDocName As String
Dim stLinkCriteria As String

strPath = CurrentProject.FullName 'db and excel file reside in same
folder

Set cnMain = New ADODB.Connection
With cnMain
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source").Value = strPath
.Open
End With

Set rsExtract = New ADODB.Recordset

With rsExtract
.ActiveConnection = cnMain
.Source = "SELECT * FROM [dbo_Table]"
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Open
End With

rsExtract.AddNew
rsExtract.Fields("CID").Value = Activecell.value 'updates field with
active cell value
rsExtract.Update

rsExtract.Close
cnMain.Close
Set rsExtract = Nothing
Set cnMain = Nothing

End Sub

 

[Guest]

Thanks, I will give that code a try! Dave

I have a database of golfers which has their handicaps listed, along with
other data. Every two weeks I get updated handicaps in an excel file. I am
able to update my access database offline and then upload the updated
database via Front Page.
However, I would rather use ADO and write some code which would read the
values from the excel file and the use the recordset update procedures via an
active server page. I use active server pages now to update addresses, show
reports, etc. but don't know how to do the update described above.

Thanks, Dave

Here is snipet of code I am using in my word form. Make sure you have ADO
reference in excel. Artem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sub Update_DB()
Dim objApp As Object
Dim strDocName As String
Dim cnMain As ADODB.Connection
Dim rsExtract As ADODB.Recordset

Dim strPath As String
Dim strTemp As String

Dim stDocName As String
Dim stLinkCriteria As String

strPath = CurrentProject.FullName 'db and excel file reside in same
folder

Set cnMain = New ADODB.Connection
With cnMain
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source").Value = strPath
.Open
End With

Set rsExtract = New ADODB.Recordset

With rsExtract
.ActiveConnection = cnMain
.Source = "SELECT * FROM [dbo_Table]"
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Open
End With

rsExtract.AddNew
rsExtract.Fields("CID").Value = Activecell.value 'updates field with
active cell value
rsExtract.Update

rsExtract.Close
cnMain.Close
Set rsExtract = Nothing
Set cnMain = Nothing

End Sub