Entry from Internet / Intranet

C

Charles Phillips

Hello,
I've inherited an MS-Excel 97 spreadsheet, as a data-entry sheet. I need to
import & link the spreadsheet into an MS-Access 97 database.
I need to keep the spreadsheet as a "data-entry" sheet, because of the mass
amount of data it contains, & the number of people using the spreadsheet.

1. How can I link the spreadsheet to an MS-Access 97 database?
2. Is it possible to convert the spreadsheet to be Internet/Intranet
compatible??
3. If it is possible to convert the spreadsheet, how & how do I maintain the
link to the MS-Access 97 database???

Can someone point me to an example/sample(s) of my request???


Thank you,

Charles L. Phillips
 
D

DDonnachie

Hi

I'm not familiar with Office'97 as far as this goes but, to connect to the
Access database u could use an ADO connection. I have used this code many
times in Excel 2003. Its not mine, found on internet. There is a similar set
of code to extract data from Access into Excel

sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cnxcn As ADODB.Connection
Dim rcrdst As ADODB.Recordset
Dim r As Long
Application.Windows(glbl_wrk_book).Activate
' connect to the Access database
Set cnxcn = New ADODB.Connection
cnxcn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "data
cnxcn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "data
source=d:\Batch_sheet_Db.mdb;"
' open a recordset
Set rcrdst = New ADODB.Recordset 'Adding in comment fields to
Btch_Cmmnts table
rcrdst.Open "BtchCmmnts", cnxcn, adOpenKeyset, adLockOptimistic,
adCmdTable
' all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Worksheets("TX_Access").Range("g" & r).Formula) > 0
' repeat until first empty cell in column g
With rcrdst
.AddNew ' create a new record
' add values to each field in the record
.Fields("CmmntStartOfShift") =
Worksheets("Tx_Access").Range("e2").Value 'Keeping this as E2 no point
repeating all of the data
.Fields("CmmntPrntTm") =
Worksheets("Tx_Access").Range("d2").Value 'as a bove
.Fields("CmmntCRO") = Worksheets("Tx_Access").Range("f2").Value
'and again these values are static
.Fields("CmmntUnitOpID") = Worksheets("Tx_Access").Range("g" &
r).Value
.Fields("CmmntComment") = Worksheets("Tx_Access").Range("h" &
r).Value
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rcrdst.Close
Set rcrdst = Nothing

cnxcn.Close
Set cnxcn = Nothing
End sub
 
A

aidan.heritage

I would look at Access, then file, get external data, link tables -
this should enable you to link directly to the Excel data.
Internet/Intranet may be a problem as you will want the data to be
updateable (which it wouldn't be on the net I think)
 
Top