copy and past data from Excel sheet to Access db using code!!!

[thepunisher]

Hello

I am programming a module in VBA behind excel.

I am able to:

Create an Access Database from the VBA code in Excel (including database fields and primary keys).

Create Access Queries from the VBA code in Excel.


What I want to do now, is select/copy a chunk of data in the excel spread sheet and ‘paste append’ it into Access automatically after the blank database has been created.



I can use VBA to add data one row at a time, using the AddNew and Update methods etc. but it’s very slow. I’d like to write code to add multiple rows in one go, if this is possible

I have tried the transferSpreadsheet method+it doesnt seem to be working!


How do I do this?

Thanks in advance,
thePunisher

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[xlbo]

append to what ?? If you have a blank database, you can't APPEND to anything as there is nothing to append to.....please clarify

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[thepunisher]

Well paste to the db rather than append.

It will be a blank table.

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[GoodOmens]

I did something similar recently that may be of help to you. I needed the data from all of the excel documents in a directory to be appended to a table in my Access database. The code below links a named range in an excel document to Access. From there I ran an append query which already existed in my database - since you are creating a blank database you would probably want a RunSQL statement with an append query.

You will probably need to add a DAO 3.6 reference to your Excel VBA.

My project ran from Access so you will need to make some adjustments to reference the correct application for OpenDatabase (I did not test this from Excel).

Sub TryImport()
Dim tb As TableDef
Dim db As Database
Dim tbSource As String
Dim conn As String
Dim fPath As String

'name of excel document
fPath = "[YOUR PATH]\Range Example.xls"

Set db = OpenDatabase("[YOUR DB NAME AND PATH].mdb")

'named range will be source
tbSource = "MyRange"

'connection string for the query definition
conn = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & fPath

'create the link to the excel document
Set tb = db.CreateTableDef(tbSource)
tb.Connect = conn
tb.SourceTableName = tbSource
db.TableDefs.Append tb

'run the append query
db.QueryDefs("z Append to Contacts Table").Execute

'delete the link to the excel document from the access database
db.TableDefs.Delete tb.Name
Set tb = Nothing

db.Close
Set db = Nothing

End Sub

 

[thepunisher]

thats the problem, i dont think it runs from Excel

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[xlbo]

seems to me you don't need to append at all - you need to create a new table based on your excel file

What you are missing is that the code above CAN be run from excel but you have to set references to MS Access 1st - you can then run the code as if you were calling it from access...

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question