Excel named range to access table 1

[neemi]

I am trying to add data from a named rage which contains several coluns of data to an access table with the same columns.

I have made an ado connection to the access table and am trying to push the named range to it from excel.

I am having trouble with the sql/vba to add the named range to the table.

help will be appreciated.

My code is as below.

        rs2.Open "INSERT INTO tblDW SELECT * FROM " & NRTDData$ & ";", cn2, adOpenDynamic, adLockOptimistic

I keep getting errors such as Type mismatch etc!!!

Please help.

 

[SkipVought]

Hi,

You are working with 2 ADO databases. You must have an ADO Connection to each (the Excel and the Access) and a recordset for each.

You are going to have to play with this, as I have never done exactly this, but I have worksed in a procedure with 2 different databases having related data; the results of on query providing criteria for a second query.



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[neemi]

so wud it not be possible to dump the contents of the namedrange into the access table in one go without looping through each record and doing it row by row?

I'm sooo stuck!

 

[SkipVought]

That would be an approch.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[neemi]

This is what I am trying to acheive.

I don't want to add the records row by row as there are 18000+ records. so was hoping to insert the contents of the named range straight into the access table. But this is where I am stuck on how to do it etc! :-/

thanks for responding.

 

[xlbo]

Are they all NEW records (ie a basic APPEND query) or are you updating as well?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[neemi]

They are all new records.

 

[PHV]

So, why not using the TransferSpreadsheet method in access VBA ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[neemi]

SOLVED!!! woo hoo!!

I thought i'd post the solution ere if anyone comes across this and if they are trying to do the same, hopefully ir will help them.

   rs.Open "INSERT INTO YourTableNameInAccess SELECT * FROM [YourNamedRangeInExcel]IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", cn

where cn is your ado connection to the access table.

 

[neemi]

phv. the frontend is in excel so can't use transferspreadsheet. but using access to pull the info to.

 

[SkipVought]

neemi,

Thanks for sharing your solution. ==> *

I don't know if or when I might need to do that, but your posted solution is not one that I would not have known how to do.

This is what Tek-Tips is all about!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[neemi]

The only reason i am using excel as a frontend tool to do this is because most of the frontends at work are in Excel due to liscencing issues with Access for each user. Therefore I just use Access to pull information to from the main database servers, do a lot of my manipulation and queries there and pull it pack to Excel to report to the user.

I could probably cut out Access altogether to reduce unnecasary objects etc... but then would need to potentially do more complex sql along joining tables accross multiple ado connections, as the data is orriginally comming from 2 or 3 various databases. ie. some data from sql server and some from teradata.

I think it makes life easier just doing it within aCCESS and then just retieving the info from a single ado connection.

i would be interesred in how other people have or would have approached this for discussion.

for summary pulling data from teradata and sqlserver, into access, from excel and reporting on excel.