transfering from Excel to Access
 

I work in a real estae brokerage firm which relies heavily on Excel.
Currently, we have maxed out spreadsheets which we are looking to
transfer into Access. How can we do so efficiently and is that even
feasible?

transfering from Excel to Access
 

I an new at this but This site help me to transfer excel spreadsheet to access

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


--
ca1358


"Nancy" wrote:

I work in a real estae brokerage firm which relies heavily on Excel.
Currently, we have maxed out spreadsheets which we are looking to
transfer into Access. How can we do so efficiently and is that even
feasible?

transfering from Excel to Access
 

Hi Nancy,

Are you just looking to transfer the data into Access or re-deploy your
application into Access? If it is just transfer the data then the standard
table import/link functions very well if you have "clean" data. You can then
just build a simple front end in Excel to manage the data in Access.

Ozzy

"Nancy" wrote:

I work in a real estae brokerage firm which relies heavily on Excel.
Currently, we have maxed out spreadsheets which we are looking to
transfer into Access. How can we do so efficiently and is that even
feasible?

transfering from Excel to Access
 

thanks Ozzy