Excel & Access, Please Help

  • Thread starter Thread starter Sahak
  • Start date Start date
S

Sahak

In Excel I have Code which exports data to an Access table
from an Excel worksheet and I have code which imports data
from an Access table to a worksheet.
The Code I have for exporting to the Access it adds values
to each field in the record.
My goal is import data from Access, make changes (Edit,
Delete or add new record) and export again changed data to
Access. In my understanding before exporting changed data
to Access, a code (which I would like to have from you)
should clear or delete all old data in Access.
Please help me with this problem.

Thanks,
Sahak
 
It would be a lot easier if you posted your existing
code...

By the way, an easier alternative is to link the
spreadsheet in Access and use plain update / delete and
append queries instead (which you can run from code, if
you like..)

Another, even simpler alternative, if you don't have any
of the issues listed further down, is to have your access
application read directly from the spreadsheet (use the
ssht as a linked table in Access), rather than importing
and exporting back and forth. This would make your life a
lot easier. The downside is this scenario imposes the
following restrictions:

* Access uses that table only for reading data
* your spreadsheet is not too big
* you do not have Access users who cannot access the drive
where the ssht sits

Nikos (nyannaco at in dot gr)
 
Hi Nikos,

First of all sorry for my English.
I appreciated for time you spent to help me.
I like Excel & I make programs with Excel macros & VBA for
my relatives & friends. I have learned to do this by
myself & because of my English I'm having some problems
when I apply for Ms Excel help.
In this case I made a program for my brother's gold
manufacturing company which keeps track of customer's
addresses, gold & amount balances, inventory, bank
transactions, prints invoices & checks & so on. The
program works perfect, but there is only one problem, it
takes time to save the workbook because of big database,
even if I keep data in separate workbook. That why I would
like to keep data in Access which can keep big database &
save data too fast.

Here is the code which exports (adds) data to the Access
database. It is ok for export data for first time. For
example if I have 10 records in database, after editing a
record in Excel & exporting to Access it will become 20
records. That why I think before exporting changed data to
Access, old data in Access should be cleared or deleted.


Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As
Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\windows\Desktop\Invoice.mdb;"
Set rs = New ADODB.Recordset
rs.Open "Invoice", cn, adOpenKeyset, adLockOptimistic,
adCmdTable
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
With rs
.AddNew ' create a new record
.Fields("Date") = Range("A" & r).Value
.Fields("Inv#") = Range("B" & r).Value
.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

If you could send me a code which will solve this problem
I will be greatly appreciated

Sorry & thank you for your time,

Sahak
 
Sahak,

It is quite easy to clear table "Invoice" before you
append the updated data. It just takes 4 lines of code:

Do
rs.MoveFirst
rs.Delete
Loop Until rs.RecordCount = 0

Insert them right after the rs.open statement, where you
have already established your connection and opened the
recordset, and just before appending the data.

Good luck,

Nikos (mail: nyannaco at in dot gr)
 
Back
Top