?
Solved

Command button to import table and overwrite current table

Posted on 2003-11-25
22
Medium Priority
?
1,048 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 375 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
 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

569 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