How to create temporary tables in Access

[greenbird]

Can anyone tell me how to create temporary tables in Access?

 

[cmmrfrds]

Temporary in what sense? Describe the duration of the process. Does the temporary table need to be linked to other tables etc....

 

[greenbird]

I am trying to import Excel file into an Access table. each time i do an import, the columns from the Excel file may vary in the number of columns as well as their names. It was suggested that i use the TransferSpreadsheet function in Access using a Temporary table.

If you can suggest a solution to the original problem of importing an Excel file where the columns vary, please do so.

 

[BNPMike]

Access will automatically create a table for you. All tables are temporary. One day they'll go.

Are you asking "How do I automatically delete tables I have finished with?". I don't think Access has any specific feature to do this. You have to delete tables yourself or write code/macros to do it for you.

 

[greenbird]

Thanks for the help.

However i am trying to import the Excel file through VBA, to make a more user friendly interface; and not through the Access menu of File, Get External Data, Import ....

Do you have any other ideas?

 

[cmmrfrds]

In VBA code
1. make an ADO connection to the excel spreadsheet.
2. send an SQL statement from the recordset method.
i.e. "Select * from sheeet1"
3. read the meta data returned in the recordset fields collection. Take each field name in the collection and build an SQL table create statement.
4. run the table create statement.
5. create a query to do Insert Into the access table from the excel spreadsheet table or insert record by record whichever you prefer.
i.e "Insert Into AccessTable Select * from sheet1

 

[greenbird]

Thank you cmmrfrds for the tip. If i could let me know how to make the ADO commection to the Excel spreadsheet i would really appreciate that.

 

[cmmrfrds]

Check this site.

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJet

Excel uses the OLEDB provider for Microsoft Jet.

Here is an example of putting the excel data in an ADO recordset.


Public Function ReadExcel()
On Error GoTo ErrHandler
Dim cn As New ADODB.Connection, cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset, rs2 As New ADODB.Recordset
Dim connString As String, connString2 As String
Dim sql1 As String, sql2 As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ATestDir\myTest.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
cn.ConnectionString = connString
cn.Open connString
''cn2.Open connString

Set rs.ActiveConnection = cn
'-- sheet name = newcustomers
sql1 = "select * from newcustomers"
rs.Open sql1, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print "field value = "; rs.Fields(0).Value
End If

Exit Function
ErrHandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.Description
For Each er In cn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.Description
Debug.Print "err source = "; Err.Source
Next
End Function

 

[greenbird]

Thank you for the tip!! I will give it a try.

 

[dRahme]

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "YourNewTableName", "C:\YourDirectory\YourSpreadSheet.xls", True