10-4 ...so I'll just scrape the data into a regular table (see code below)
a couple of follow-up questions:
when linking to the Excel Workbook should I use:
strLink = "Excel 5.0;DATABASE=" & strXlTx
--or--
strLink = "Excel 8.0;DATABASE=" & strXlTx
why isn't there a Excel 11.0 option?
is it better to use:
db.Execute "DROP TABLE Sheet1"
--or--
db.TableDefs.Delete "Sheet1"
Thanks!
Public Function LinkXl()
Dim strLink As String
Dim strXlTx As String
Dim idxPk As DAO.Index
Dim idxFld As DAO.Field
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb()
'delete old tables if they exit
db.Execute "DROP TABLE Sheet1"
db.Execute "DROP TABLE tblExcelTx"
'link to Excel Workbook
strXlTx = Nz(DLookup("ExcelTx", "tblOutput"), 0)
Set tdf = db.CreateTableDef("Sheet1")
strLink = "Excel 8.0;DATABASE=" & strXlTx
tdf.Connect = strLink
tdf.SourceTableName = "Sheet1$"
db.TableDefs.Append tdf
'create tblExcelTx
Set tdf = db.CreateTableDef("tblExcelTx")
With tdf
.Fields.Append .CreateField("TxDate", dbDate)
.Fields.Append .CreateField("Amount", dbCurrency)
.Fields.Append .CreateField("Comment", dbText, 200)
.Fields("Comment").AllowZeroLength = True
.Fields.Append .CreateField("Entity_ID", dbLong)
.Fields.Append .CreateField("TxAcct_ID", dbLong)
.Fields.Append .CreateField("TxType_ID", dbLong)
.Fields.Append .CreateField("Payment_ID", dbLong)
.Fields.Append .CreateField("Etx_ID", dbLong)
.Fields("Etx_ID").Attributes = dbAutoIncrField
End With
Set idxPk = tdf.CreateIndex("Etx_ID")
idxPk.Primary = True
idxPk.Unique = True
Set idxFld = idxPk.CreateField("Etx_ID")
idxPk.Fields.Append idxFld 'add field to Fileds collection of index
object
tdf.Indexes.Append idxPk 'add index to index collection
db.TableDefs.Append tdf 'append tdf to TablsDefs collection
'append into tblExcelTx all records from Sheet1
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryTxAppendXl"
DoCmd.SetWarnings True
'set recordsource on frmTxAssign
Form_frmTxAssign.SetRs
'drop Linked table
db.Execute "DROP TABLE Sheet1"
Set db = Nothing
Set tdf = Nothing
End Function
Link the Workbook and scrape the data out into a table I can work with then
drop it
"onedaywhen" <on********@fmail.co.uk> wrote in message
news:b8**************************@posting.google.c om...
The DELETE keyword is not supported *by design* for the ISAM for
Excel. Remember that unlike a RDBMS table, the row order in Excel does
matter.
--
"deko" <dj****@hotmail.com> wrote in message
news:<Fx******************@newssvr29.news.prodigy. com>...
How to run action query against linked table?
I have an Access 2003 mdb with an Excel 2003 Workbook as a linked table.
When I attempt to run an action query against the linked table I get
this error:
Deleting data in a linked table is not supported by this ISAM.
From what I understand, indexed sequential access method (ISAM) drivers
are used to update "non-Microsoft" file formats. So why doesn't Access
recognize the Excel file format - an Office System sibling? I'd think
there would be compatibility here.
Perhaps my code is incorrect.
Here's how I link the Excel Workbook as a table:
Public Function LinkXl()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strLink As String
Dim strXlTx As String
strXlTx = Nz(DLookup("ExcelTx", "tblOutput"), 0)
Set db = CurrentDb()
db.TableDefs.Delete "Sheet1"
Set tdf = db.CreateTableDef("Sheet1")
strLink = "Excel 5.0;DATABASE=" & strXlTx
tdf.Connect = strLink
tdf.SourceTableName = "Sheet1$"
db.TableDefs.Append tdf
Set db = Nothing
Set tdf = Nothing
End Function
Here's the query that results in the error mentioned above:
DELETE *
FROM Sheet1
WHERE TxDate Is Not Null AND Amount Is Not Null AND Entity_ID = 489 AND
TxType_ID =20 AND txAcct_ID = 91;
Am I missing something? Is there a work-around to be able to run action
queries against linked tables?
Thanks!
