sql insert into named range from access

mferrisi
mferrisi used Ask the Experts™
on
Hello,

I have successful run an 'insert' query by querying from a named range and then inserting the rows into a table in access (while both documents are closed). I am having difficulty doing the reverse--querying from an access table and then inserting the data via query into a named range. My unsuccessgful attempt is shown below.

Thank you!
This works:
 
wbPathName = "M:\Path\path.xls
ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & wbPathName & ";" & _
    "Extended Properties=Excel 8.0;"
    
dbPath = "X:\Matt\Jun2009.mdb"
 
strSQL = "INSERT INTO [" & tableName & "] IN '" & dbPath & "' Select * FROM " & ExportRange
Set cnt = New ADODB.Connection
    With cnt
        .Open ConnectionString
        .Execute strSQL
    End With
 
This does not:
wbPathName = "X:\Matt\Jun2009.mdb"
ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & wbPathName & ";" & _
    "Extended Properties=Excel 8.0;"
    
dbPath = "M:\Path\path.xls" 
 
strSQL = "INSERT INTO ExportRange " & dbPath & "' Select * FROM " & tableName
Set cnt = New ADODB.Connection
    With cnt
        .Open ConnectionString
        .Execute strSQL
    End With

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I think you might have the wrong connection string.  Have a look here:
http://www.connectionstrings.com/excel

Let me know if you need anything further.
-David251

Author

Commented:
If I remove the INSERT INTO statement and just run the Select portion, I am able to retrieve data. Thus, I am guessing that the issue comes from attempting to insert into a named range. Do you have any examples were you are able to do this (querying from access and inserting into a named range)?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Try this:
wbPathName = "X:\Matt\Jun2009.mdb"
ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & wbPathName & ";"
    
dbPath = "M:\Path\path.xls" 
 
strSQL = "INSERT INTO ExportRange IN '" & dbPath & "' Select * FROM " & tableName
Set cnt = New ADODB.Connection
    With cnt
        .Open ConnectionString
        .Execute strSQL
    End With

Open in new window

Author

Commented:
I get an error that Says "Unrecognized database format 'M:\Path\path.xls'
Try using the following link's syntax then:
http://support.microsoft.com/kb/295646 

Commented:
try changing this
strSQL = "INSERT INTO ExportRange " & dbPath & "' Select * FROM " & tableName
to this
strSQL = "INSERT INTO [ExportRange$]  Select * FROM " & tableName

-David251

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial