Solved

Command button to import table and overwrite current table

Posted on 2003-11-25
22
992 Views
Last Modified: 2007-12-19
I have an excel file that I want to import and have it replace a table that is in the database already - I will be preformatted in excel to the same colums headings etc etc.
0
Comment
Question by:SuprSpy79
  • 12
  • 10
22 Comments
 
LVL 5

Expert Comment

by:morpheus30
ID: 9819566
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel10, "NameOfYourTable", "NameOfYourFile; If it's in My Documents, you don't have to specify full path name, just the file name", True
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9819585
The TRUE value above means that your spreadsheet has column names on the first row.  Be sure to name them the same as your table...

For what it's worth, you could also link the Excel spreadsheet to Access as a linked table and query it like an Access table.  That will give you more flexibility as you can pick and choose which fields you want to INSERT into your Access table.
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9819599
Oh, yes...  I forgot.  The above method will CREATE a table, it won't append to an existing table...
0
 
LVL 5

Author Comment

by:SuprSpy79
ID: 9819605
Can you make it prompt for a file path?
0
 
LVL 5

Author Comment

by:SuprSpy79
ID: 9819635
I want it to OVERWRITE an existing table
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9819655
Dim strFile As String

strFile = InputBox("Enter the path of your file...", "File path", "")

DoCmd.SetWarnings False
DoCmd.RunSQL "TRUNCATE TABLE TableName"
DoCmd.SetWarnings True

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel10, "NameOfYourTable", strFile, True
0
 
LVL 5

Author Comment

by:SuprSpy79
ID: 9819726
Close - how bout a browse button on the file search?
0
 
LVL 5

Author Comment

by:SuprSpy79
ID: 9819767
Im getting run-time error '3129':

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'



Private Sub Command21_Click()
Dim strFile As String

strFile = InputBox("Enter the path of your file...", "File path", "")

DoCmd.SetWarnings False
DoCmd.RunSQL "TRUNCATE TABLE tblData"
DoCmd.SetWarnings True

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel10, "tblData", strFile, True
End Sub
0
 
LVL 5

Accepted Solution

by:
morpheus30 earned 125 total points
ID: 9819796
Sorry...  I forgot TRUNCATE is a T-SQL statement....

Private Sub Command21_Click()
Dim strFile As String

strFile = InputBox("Enter the path of your file...", "File path", "")

DoCmd.SetWarnings False
DoCmd.RunSQL "DROP TABLE tblData"
DoCmd.SetWarnings True

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel10, "tblData", strFile, True
End Sub
0
 
LVL 5

Author Comment

by:SuprSpy79
ID: 9819820
run-time error '3211';

The database engine could not lock the table 'tblData' because it is already in use by another person or process.
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9819839
The form that your command button is on, is it bound to "tblData"?  If so, this is what's causing the error.  You'll have to create a new form that is NOT bound to this table in order for the code to work
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:SuprSpy79
ID: 9819913
Ok got it working off another form - now is there a way to have a browse button so they can browse for the file?
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9819927
0
 
LVL 5

Author Comment

by:SuprSpy79
ID: 9820017
Im not sure what it meants by inserting the common dialog control - im assuming i would have to have a browse button you would have to hit first and browse for the file and then it will display - but then how do you tell the import button created earlier to use that path?
0
 
LVL 5

Author Comment

by:SuprSpy79
ID: 9820030
ok i tried instering the activex control and its saying im not licensed to do that
0
 
LVL 5

Author Comment

by:SuprSpy79
ID: 9820099
I posted about the activex control on the other thread - but for now - if I hit the cancel but on the import I get a runtime error - how do I make cancel skip to the end of my commands that close the form and open back up my main form?

Private Sub cmdImportFinal_Click()
Dim strFile As String

strFile = InputBox("Enter the path of your file...", "File path", "")

DoCmd.SetWarnings False
DoCmd.RunSQL "DROP TABLE tblData"
DoCmd.SetWarnings True

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel10, "tblData", strFile, True
MsgBox "Import Successful", vbOKOnly + vbExclamation, "Import Results"

Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSearch"
    DoCmd.Close
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
0
 
LVL 5

Author Comment

by:SuprSpy79
ID: 9820294
I figured it out

Made a text box called txtFile

Then made a browse button using
Application.FileDialog(msoFileDialogFilePicker)

then a button to execute the import

Private Sub cmdImport_Click()
 If MsgBox("Are you sure?", vbYesNo, "Import file") = vbNo Then End
   
    On Error GoTo errorme
       
    txtFile.SetFocus
DoCmd.SetWarnings False
DoCmd.RunSQL "DROP TABLE tblData"
DoCmd.SetWarnings True

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel10, "tblData", txtFile.Text, True, ""
MsgBox "Import Successful", vbOKOnly + vbExclamation, "Import Results"
Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSearch"
    DoCmd.Close
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Exit Sub
       
errorme:

MsgBox "Error!!  Make sure you use the browse button to select a valid excel file"

End Sub



Thanks for your help
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9820581
Hey SuprSpy79:

Sorry I couldn't get back to you sooner, I was out on my lunch break...

Glad you got your problem fixed...


0
 
LVL 5

Author Comment

by:SuprSpy79
ID: 9820828
no problem - I still have one issue though - the import doesnt work unless the table exists - so when I delete it before the import - if there is an error somehow and it doesnt import - then the table is gone - and i cant run the import again until i manually recreate the table structure

im wondering if instead of

DoCmd.SetWarnings False
DoCmd.RunSQL "DROP TABLE tblData"
DoCmd.SetWarnings True

I can use

currentdb.execute "DELETE * FROM tblData;"
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9820849
Yes you could do that.  That will not delete the table, but only the data inside of it...
0
 
LVL 5

Author Comment

by:SuprSpy79
ID: 9820863
yeah thats ok though because the import should add the data into the existing table anyway - right?
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9820887
That's right!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now