Solved

Command button to import table and overwrite current table

Posted on 2003-11-25
22
1,021 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

828 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