Delete existing Access table and create a new table from Excel sheet with a command button from the form

Something like:

CurrentDb.Execute "DROP TABLE tblMyTableNameHere"
DoEvents
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblMyTableNameHere", "C:\myExcelFile.xls", -1, "Sheet!A1:B100"

Change tblMyTableNameHere to your table name, C:\myExcelFile.xls to the location of the file name and Sheet!A1:B100 to the range of where this data is.

Post back if you get stuck (first take a look at TransferSpreadsheet  in the help)

Dave

Dave
Thanks. Only two things:
1) I would like to delete,  if it exists to avoid error condition.
2) Can the upload be not limitted from  predefined range cell (A1:B100). Further can I load field by field so that I could perform some edit or validation while creating  table.

Cheers

1.

We could loop to see if it's there:
Dim tDef As DAO.TableDef

For Each tDef In CurrentDb.TableDefs
    If tDef.Name = "tblMyTableNameHere" Then
        CurrentDb.Execute "DROP TABLE " & tDef.Name
    End If
Next

'// Second part

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblMyTableNameHere", "C:\myExcelFile.xls", -1, "Sheet1"

Where Sheet1 is the sheet name

>Further can I load field by field so that I could perform some edit or validation while creating  table.
What do you mean by this?

Dave

Dave
for the first part i have the following code (which is just copied from your mail.
Private Sub Command2_Click()
   Dim tDef As DAO.TableDef ( i have used both def and defs)
    For Each tDef In CurrentDb.TableDef
        If tDef.Name = "DROP TABLE tblDDT_trans" Then
            CurrentDb.Execute "DROP TABLE " & tDef.Name
        End If
    Next
End Sub
iam gettting the compiler error on
 Dim tDef As DAO.TableDef


For the 2nd part, I would like to create the table by processing sheet row by row and placing the values in the table fields (part of the vba code) and inserting the record in the table

>iam gettting the compiler error on

In the VBE, select Tools -> References and tick Microsoft DAO Object Library 3.6

>I would like to create the table by processing sheet row by row and placing the values in the table fields (part of the vba code) and inserting the record in the table

Why, when TransferSpeadsheet does it for you all at once? I can't understand the logic (I'm probably missing something..)

Dave

<off topic>
Hey Dave nice to see you around, and sorry about the "unpro" comment in your profile :(

jaffer

Thanks for the help. Reason for row by row transfer:
I would like to define additional fields in the row  as I am doing the transfer. These fields may be filed in based on certain elements of the row and some mapping table.
For example I have 3 Columns a,b,c in the Excel sheet. and there is mapping table containing the mapping values for b
in the process I would like to define one additional field d   based on the mapping value of b in the maping table as I am processing the row.
Cheers

Hi Amjad

Please see this link to get an idea of what you want to do:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23189542.html#20976181

jaffer

Thanks jaffar.

It is a good example to create the spread sheet from the access table. I am trying to do the other way around, i.e ; create access table from Excel sheet. sometheing like statement sql = "select * from [abc$]"

Hi Amjad

This code will read every cell in your xls file,
using Recordset, you can save it to Access Table.

OR

Save your Excel file as a comma delimited (csv file),
then read it in code line by line, and using Recordset, you can save it to Access Table.

Dim TextLine
Open "SaveFileAs" For Input As #1    ' Open file.
Do While Not EOF(1)    ' Loop until end of file.
    Line Input #1, TextLine    ' Read line into variable.
    Debug.Print TextLine    ' Print to the Immediate window.
Loop
Close #1    ' Close file.


jaffer

'make Reference to Microsoft Excel x.xx Object Library
 
    Dim xl As Object
    Dim sht As Object
 
    Set xl = CreateObject("excel.application")
    Set bok = xl.Workbooks.Open(Application.CurrentProject.Path & "\ExcelSample_original.xls")
    'Set xl = Workbooks.Open(Application.CurrentProject.Path & "\Excelsample.csv")
    Set sht = xl.ActiveSheet
    xl.Visible = False  'True
    
nRow = 0
For Each rwRow In sht.Rows
nRow = nRow + 1
 
 nLastColumn = 0
 For nColumn = 1 To sht.Columns.Count
  If sht.Cells(nRow, nColumn) <> 0 Then
    nLastColumn = nColumn
  End If
 Next nColumn
 
 For nColumn = 1 To nLastColumn
 MsgBox sht.Cells(nRow, nColumn)
 Next nColumn
 
Next rwRow
 
'    SaveFileAs = Application.CurrentProject.Path & "\Excelsample.xls"
'    bok.SaveAs Filename:=SaveFileAs, FileFormat:=xlNormal
        
        
    xl.ActiveWorkbook.Close -1
    xl.Quit
    
    Set xl = Nothing
    Set bok = Nothing
    Set sht = Nothing
 
 
 
 
 
OR
 
 
'make Reference to Microsoft Excel x.xx Object Library
 
    Dim xl As Object
    Dim sht As Object
 
    Set xl = CreateObject("excel.application")
    Set bok = xl.Workbooks.Open(Application.CurrentProject.Path & "\ExcelSample_original.xls")
    'Set xl = Workbooks.Open(Application.CurrentProject.Path & "\Excelsample.csv")
    Set sht = xl.ActiveSheet
    xl.Visible = False  'True
    
    SaveFileAs = Application.CurrentProject.Path & "\Excelsample.xls"
    bok.SaveAs Filename:=SaveFileAs, FileFormat:=xlCSV
 
    xl.ActiveWorkbook.Close -1
    xl.Quit
    
    Set xl = Nothing
    Set bok = Nothing
    Set sht = Nothing

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:

Thanks Jaffar. The above really has helped. since I am importing  a number of data items, I am getting a overflow error on the following input Statement

"Input #FileNum, Account, AccountName, RCNumber , RCName, PDDate, DOCType, SAPnumber, Quantities, Amount, Headertext, SANumber, DocNo, Itemtext"

How to break the code into multiple lines I have tried "&_" and "_" it does not accept it.
How can we increase the points for this solution. I think it should be 250 plus

Hmmmm
1.
if you can show me the whole picture, then I could help further,
please show me the whole code, and tell me what this code does when you get the error.
2.
maybe the overflow error is due to the size of the field in your table, where it is small and not enough to take the whole input data.
3a.
You can break the line with _
like this
& _       OR      , _
notice the space before the _
BUT if you have your line between quotes " ", then it cannot be done, as it is considered as one line, to break it, do this:
"Hello my name is jaffer" _
"and I am helping Amjad"
3b.
I haven't done the point thing for a long time, but I assume there should be 2 ways to do it,
a. look around this page, there might be a place to control the points,
b. post a request in CS to increase the points:
http://www.experts-exchange.com/Community_Support/General/

jaffer

Jaffar
Following is the snap shot of the code. It is giving me syntax error on input #num
'Opening the file to read from
FileNum = FreeFile()
InputFile = "D:\DTTUS.csv"        '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
 
Line Input #FileNum, InputString     '<--- To throw away the header line
Do Until EOF(FileNum) = True
     Input #FileNum, Account, AccountName, RCNumber,&_
     RCName , PDDate, DOCType, SAPnumber, Quantities, Amount, Headertext, SANumber, DocNo, Itemtext
     ' personnelNo, WBSElement
          'Read the data in
          'the input is an empty string write it
     With RS
        .AddNew
        !Account = Account
        !AccountName = AccountName
                     !RCNumber = RCNumber
   '                  !RCName = RCName
   '                  !PDDate = PDDate
   '                 !DOCType = DOCType
   '                  !SAPnumber = SAPnumber

Hi Amjad

I cannot see anything wrong with your code, so I assume the following:
1.
To prevent the overflow error change the Integer in the Dim statement to Long,
like this:
istead of
    Dim intCnt1 As Integer
To
    Dim intCnt1 As Long

and change the DataType of that field too, from Integer to Long,

2.
it can be due to a Null in the data, where you specified in the field in the Table that Nulls are not accepted,

3.
if the above didn't help, then I will have to llok at your trimmed mdb and the excel file.

jaffer

Hi Jaffar,
You have been a great help. One last item on this issue.
While save the file as CSV, is there a way to drop comma (,) from the numeric field if it has comma.  Because CSV assumes a next field in the item string after comma

Hi Amjad

I am afraid not!
it is something Excel does automatically,
however, Excel should not have comma's in the values, as the comma is the thousand seperator, which is just a format Excel does, just to show the user, but not saving the values internally, I think!

If you don't want to save the Excel file with comma delimited, then you can save it with TAB delimited (that is the seperator between the fields),
so instead of using
    bok.SaveAs Filename:=SaveFileAs, FileFormat:=xlCSV
use
    bok.SaveAs Filename:=SaveFileAs, FileFormat:=xlText

If this didn't work, I would give you 2 options:
1a. use Flavo's suggestion to import the Excel data to an Access Temporary Tabble (by the way, I consider Flavo "Dave" as one of the best EXPERTS, he even can write code while he is asleep ;)
1b. use append query OR Recordset, to copy the right data to the correct table.

OR
2. use the 1st option I gave you, that is to go though the Excel file field by field, and use the Replace command to replace the comma's with anything else you like,
infact, maybe we can automate the csv file, with the Replace, like this (this is air code, so it might need adjustment) :


jaffer

Dim TextLine
InputFile = "D:\DTTUS.csv" 
Open InputFile For Output As #1
 
nRow = 0
....
TextLine=""
 For nColumn = 1 To nLastColumn
 TextLine=TextLine & Replace(sht.Cells(nRow, nColumn),",","") & ","
'we are replacing the comma's with nothing             ^   ^
 Next nColumn
 
 Print #1, mid(TextLine,1,len(TextLine)-1)
 
Next rwRow
 
Close #1
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:

Thanks. I was busy in some thing else. The solution workede as it was supposed to.
Thanks again for your help.

>The solution workede as it was supposed to
Oh Come on,
Why then the B grade????
this is really unfair!!!!!!

Please see:
What's the right grade to give?
http://www.experts-exchange.com/help.jsp#hi97

This was my first time on this ( as matter of fact on any). It was just clicked with realizing how it works. I am ready to upgrade to "A" if possible.. I will be more conscious in awarding the grade and points in future.

Thanks again for your help !!!!!!!!!
Cheers.

Hi Amjad

Thanks for your reply, and I am glad I was of help to you :)

We are volunteers to help others, like you,
and we "Experts" compete with each other for total points (points x grade).

I have requested CS to look into this Q for a grade change.


jaffer