Transferring Excel Spreadsheets into Access

[Nancy]

I work in a real estate brokerage firm. We need to transfer our
current excel based spreadsheet into Access. Our goal is to utilize
Access for superior Database Management while maintaining Excel's
calculator functions. Is this feasible? Also can this be done in an
HTML environment?

 

[paqogomez]

If you want "superior Database Management" dont do it in Access. If
you are going to make the leap, leap to something that cannot corrupt
or get screwed up as easy as Access can.

For a Microsoft solution this would be SQL Server. MySQL might be a
good solution as well if the price point for SQL Server is too high.

I can understand that Access might seem like something easy to jump
into, and if you have no one that can administor the previous
suggestions, then it might be your only alternative.

As for HTML environments you'll need to get into a programming language
to be able to do what you want.

It is my opinion that a home grown solution in Excel and Access will
not allow you to grow with your data. While a custom application may
be expensive, it will give you visiblity into your data that you cannot
achieve with Excel/Access. That said, there are also off the shelf
apps that may serve that purpose as well.

There may be others in this group that can give you alternatives, this
is just my two bits.

paqo

 

[Nancy]

thank you very much

 

[Guest]

This site might help you.

http://www.exceltip.com/se/subtotal.html

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[Nancy]

thank you