Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Command button to import table and overwrite current table

Posted on 2003-11-25
22
Medium Priority
?
1,045 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

604 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