apend an access table from excel

[Joe C]

Can I append an acces table through vba.

I have retireved queries through ado and want to create a similar report but I need the user to append table for lastest monthly data each time user runs.
Is it possible to append the table using VBA ADO or someway. I would rather not have the person have to open access themself.

 

[Richard Schollar]

Re: apend a access table from excel

Hi

Assumptions:
Access database path & name: Y:\Work\db1.mdb
Table Name: Table1
Fields: ID, Field1

Then you can use the following code to update from Excel to Access:

Sub t4()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
With cnn
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Y:\Work\db1.mdb;User Id=admin;Password=;"
    .CursorLocation = adUseServer
    .Open
End With

rst.Open "Table1", cnn, adOpenForwardOnly, adLockOptimistic, adCmdTable
For Each cell In Range("A2:A4")  'assumes IDs are in A2:A4 and Field1 names in B2:B4
  With rst
      .AddNew
      !ID = cell.Value :     !Field1 = cell.Offset(,1).Value
      .Update
  End With
Next cell
End Sub

 

[Joe C]

Re: apend a access table from excel

Many thanks Going to give it a try this morning.

Usually my tries go well into the night, but at least I am having fun.

 

[Fazza]

Re: apend a access table from excel

Hi. Some further discussion & options - http://www.mrexcel.com/forum/showthread.php?t=332472 HTH, Fazza

 

[Joe C]

Re: apend a access table from excel

i think I have this working just want to make sure.
This is adding a new record for each?

 

[Richard Schollar]

Re: apend a access table from excel

The code I gave was adding a new record for each cell in range A2:A4 (ie 3 records in total). Two fields were added for each record.

 

[Joe C]

Thats what I thought. If there were more fileds I would just add them.
But it does not make sure the record is nuique?

 

[Richard Schollar]

No, the code performs no checking of the records that are added - this would have to be coded in if you need to do this. Something to be aware of is that if you have an Autonumber field in your data table in Access, then I'm pretty sure you can't use the AddNew method to add a new record (you have to go another route and use an INSERT SQL statement).

 

[NateO]

You could Filter the Recordset to see if you're updating an existing record, or adding a new one, e.g.,

http://www.utteraccess.com/forums/showflat.php?Number=1415643&fpart=3

Not quite sure how that would work with Autonumber...