SQL syntax for exporting a named Excel range into Access (from Excel)

Hello

I want to export a named range in Excel to an Access table and have trouble with the syntax of the SQL Insert Into statement. I Guess the problem is the FROM [SourceTable] part.

See code below:
Private Sub sCommandTextMultiRecords()
Dim sTableName As String
Dim SourceTable As Range

sTableName = [sDBtabell1] ' The name of the Access Table: "Driftsregnskap"
Set SourceTable = wksCA_Driftsregnskap.[CA_Table] 'Excel Named Range in active workbook

sCommandText = "INSERT INTO " & sTableName & "SELECT * FROM [SourceTable] ;"
Debug.Print sCommandText

End Sub

Open in new window


I would highly appreciate a quick response!

Best
Ketil
MomentumConsultingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

krishnakrkcCommented:
Hi

" INSERT INTO " & sTableName & vbLf & _
" SELECT * FROM [CA_Table]" & vbLf & _
" IN '" & wksCA_Driftsregnskap.parent.fullname & "'[Excel 12.0 Macro;HDR=Yes;]"

Open in new window


Kris
0
MomentumConsultingAuthor Commented:
Hi

Thanx for quick response. However, the code crashed :(
I get the following Error Message:

"Can't find installable ISAM"
(Translated from Norwegian)

Any idea?
0
krishnakrkcCommented:
Hi

This is not the direct answer to your question, but have a look at the following page and you can trouble shoot the connection strings according to your version.

http://www.connectionstrings.com/excel/

Kris
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

MomentumConsultingAuthor Commented:
Hi

I think I got a it further but still no success.
After reviewing the connectionstrings site I changed from Excel 12.0 Macro to Excel 8.0, like this:
" INSERT INTO " & sTableName & vbLf & _
" SELECT * FROM [CA_Table]" & vbLf & _
" IN '" & wksCA_Driftsregnskap.parent.fullname & "'[Excel 8.0 ;HDR=Yes;]" 

Open in new window


Now I get the following Error Message:
"Microsoft Jet Database Engine can't find the object CA_Table. Check that the Object exist and that the name and path is spelled correctly."
(Translated from Norwegian)

I have tried to substitute [CA_Table] with [SourceTable] but I get the same error.

The code now looks like this:
Private Sub sCommandTextMultiRecords()
Dim sTableName As String
Dim SourceTable As Range

sTableName = [sDBtabell1] '"Driftsregnskap": The name of the Access Table
Set SourceTable = wksCA_Driftsregnskap.[CA_Table] 'Excel Named Range

sCommandText = " INSERT INTO " & sTableName & vbLf & _
                " SELECT * FROM [SourceTable]" & vbLf & _
                " IN '" & wksCA_Driftsregnskap.Parent.FullName & "'[Excel 8.0;HDR=Yes;]"                 ''
Debug.Print sCommandText

End Sub

Open in new window


I have also tried to substitute [SourceTable] with
" & SourceTable & " and
[" & SourceTable & "]
but no success...

From the immidiate window:
 INSERT INTO Driftsregnskap
 SELECT * FROM [CA_Table]
 IN 'C:\Users\Ketil Pedersen\Documents\MOMENTUM\Kunder\Selvkost\Ketils kunder\L - O\Oslo kommune EGE\Virksomhetsstyringsmodell\EGE totalmodell v4.xlsm'[Excel 8.0;HDR=Yes;]

And:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Ketil Pedersen\Documents\MOMENTUM\Kunder\Selvkost\Ketils kunder\L - O\Oslo kommune EGE\Virksomhetsstyringsmodell\EGE_virksomhetsstyring.mdb;Persist Security Info=False;Jet OLEDB:Database Password=EK1411;

I can inform that the named range is a dynamic range, meaning that it is determined by an Offset-function where the height argument is determined by a Counta-function. Do you think this can create the error?

Any other ideas?

Ketil
0
krishnakrkcCommented:
I think the dynamic named range won't work.

Give this a try.

http://www.excelfox.com/forum/f13/export-data-from-excel-to-access-table-ado-using-vba-182/

Kris
0
MomentumConsultingAuthor Commented:
From the very long sub you linked to I gather that the syntax
[" & SourceTable & "]
should be good. :)

(From Excelfox:
"INSERT INTO " & CStr(Tbl_Name) & " SELECT * FROM [" & arrNameRanges(lngLoop) & "] IN '" & strTempFullName ....)

I have not created the looped array in the excelfox-solution but I have substituted the dynamic range CA_Table with a fixed range CA_Table2. Still I get an Error Message:
Type mismatch (run time error 13)
when I run this code:
Private Sub sCommandTextMultiRecords()
Dim sTableName As String
Dim SourceTable  As Range

sTableName = [sDBtabell1] '"Driftsregnskap": The name of the Access Table
Set SourceTable = wksCA_Driftsregnskap.[CA_Table2] 'Excel Named Range

sCommandText = "INSERT INTO " & sTableName & " SELECT * FROM [" & SourceTable & "] IN '" & wksCA_Driftsregnskap.Parent.FullName & "'[Excel 8.0;HDR=Yes;]"
Debug.Print sCommandText

End Sub

Open in new window


Ketil
0
krishnakrkcCommented:
Hi

Copy the procedure in a standard module and call the sub by passing the required parameters.

Try

ExportRangeIntoAccess "C:\Users\Ketil Pedersen\Documents\MOMENTUM\Kunder\Selvkost\Ketils kunder\L - O\Oslo kommune EGE\Virksomhetsstyringsmodell\","EGE_virksomhetsstyring.mdb",  "Driftsregnskap", wksCA_Driftsregnskap.name, True, AppendTable, range("CA_Table2").address, "", false
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MomentumConsultingAuthor Commented:
Hi Kris and thanks a lot!

That sub did the trick! I just had to change this part:
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & CStr(strDBFullName) & ";"

Open in new window

whit this part:
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & CStr(strDBFullName) & ";Persist Security Info=False;Jet OLEDB:Database Password=" & sDbPassword & ";"

Open in new window

I needed my database password!

By the way, the named range does not have to be fixed, it works well with a dynamic range.

Again, thanks a lot, now I don't have to use the slow single record procedures from the past :)

Ketil
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.