By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

How to Import selected columns from an Excel to MS ACCESS?

P: 66
Hi All,

I would like to export selected columns from an excel sheet to MS Access. All the columns are not together, they are B, C, Z and AA.

I used Transferspreadsheet but only ranges without gaps can be achieved in it as per my review

I used SQL query code as below, even that shows an error as "Too few parameters, expected 4". Please help me out to successfully complete this import.

Expand|Select|Wrap|Line Numbers
  1. ssql = "INSERT INTO [MS Access;DATABASE=C:\Users\" & usrnm & "\Desktop\Aging_Report.accdb].[COMMERCIAL_DETAIL] ( [SUPERVISOR NAME], [PRODUCT], [AGE], [STATUS] ) SELECT [COMMERCIAL_DETAIL$].[b], [COMMERCIAL_DETAIL$].[C], [COMMERCIAL_DETAIL$].[Z], [COMMERCIAL_DETAIL$].[AA] FROM [COMMERCIAL_DETAIL$]"
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, 10, "COMMERCIAL_DETAIL", Form_frmRptUpld.RPath.Value, True, "B:C,Z:AA"
Jan 20 '15 #1

✓ answered by johny6685

OK!!! I did the below way and it worked out. Thanks anyways....

Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. DoCmd.RunSQL "DELETE * FROM TABLEA"
  3. DoCmd.TransferSpreadsheet acImport, 10, "tblTempPartSearch", Form_frmRptUpld.RPath.Value, True, "B:AA"
  4. 'DoCmd.OpenQuery "QryApndTABLEA"
  5. DoCmd.RunSQL "INSERT INTO TABLEA ( F1, f2, f3, f4 ) SELECT tblTempPartSearch.[f1], tblTempPartSearch.f2, tblTempPartSearch.f3, [tblTempPartSearch].f4 FROM tblTempPartSearch"
  6. DoCmd.RunSQL "DROP TABLE tblTempPartSearch"
  7.  
  8. DoCmd.SetWarnings True

Share this Question
Share on Google+
1 Reply


P: 66
OK!!! I did the below way and it worked out. Thanks anyways....

Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. DoCmd.RunSQL "DELETE * FROM TABLEA"
  3. DoCmd.TransferSpreadsheet acImport, 10, "tblTempPartSearch", Form_frmRptUpld.RPath.Value, True, "B:AA"
  4. 'DoCmd.OpenQuery "QryApndTABLEA"
  5. DoCmd.RunSQL "INSERT INTO TABLEA ( F1, f2, f3, f4 ) SELECT tblTempPartSearch.[f1], tblTempPartSearch.f2, tblTempPartSearch.f3, [tblTempPartSearch].f4 FROM tblTempPartSearch"
  6. DoCmd.RunSQL "DROP TABLE tblTempPartSearch"
  7.  
  8. DoCmd.SetWarnings True
Jan 20 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.