?
Solved

Command button to import table and overwrite current table

Posted on 2003-11-25
22
Medium Priority
?
1,039 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

765 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