A little help in getting selected info from Excel to Access

corporateaccount

Board Regular
Joined
Aug 11, 2004
Messages
64
Hello all, I've done a search on this and I believe I am on the right track.

Basically, all I need to do is take selected information from an Excel workbook and copy it to a closed Access file.

I've found this code:

Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb")
' open the database
Set rs = db.OpenRecordset("TableName", dbOpenTable)
' get 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
db.Close
Set db = Nothing
End Sub

Which takes a stream of information until it ends and places it on a mdb file. What I would need is just to take five or six selected pieces of information and add them in. I've tried to simplify the code to something like this, but it does not work.

Sub DAOFromExcelToAccess()

Dim db As Database, rs As Recordset
Set db = OpenDatabase("C:\FolderName\DataBaseName\db1.mdb")

Set rs = db.OpenRecordset("TableName", dbOpenTable)



With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Name") = Sheet2.Cells(25, 1).Value
.Fields("Address") = Range(26, 1).Value
.Fields("Phone") = Range(27, 1).Value

.Update ' stores the new record
End With

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub


Can anyone please help me out here?
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

corporateaccount

Board Regular
Joined
Aug 11, 2004
Messages
64
For those wondering, I figured it out!

Here is what I did.

Sub data()
Dim db As Database, rs As Recordset
Set db = OpenDatabase("C:\Documents and Settings\Administrator\Desktop\db1.mdb")
Set rs = db.OpenRecordset("USAClaims", dbOpenTable)

With rs
.AddNew
.Fields("Name") = Sheet1.Cells(15, 1).Value
.Fields("InsCo") = Sheet1.Cells(5, 16).Value
.Fields("DOL") = Sheet1.Cells(10, 28).Value

.Update
End With

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub



And this is with the MS DAO Object Library selected (and set to a higher priority).

Works great!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,987
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top