By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,037 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Updating Linked Table - why is action query failing?

P: n/a
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!
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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!

Nov 12 '05 #2

P: n/a
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!

Nov 12 '05 #3

P: n/a
"deko" <dj****@hotmail.com> wrote in message news:<Lz*******************@newssvr29.news.prodigy .com>...
a couple of follow-up questions:
Replies inline:
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?
Not sure why there isn't an Excel 11.0 option. Excel 8 is Excel97, a
major revision and the version from which backwards compatability
commenced. So use Excel 5.0 is for pre-Excel97 workbook formats,
Excel 5.0 for more recent versions.
is it better to use:
db.Execute "DROP TABLE Sheet1"
--or--
db.TableDefs.Delete "Sheet1"


Dunno, I don't do DAO! DROP TABLE in ADO works for me (clears the rows
and column headers but worksheet remains in the workbook).

--
Nov 12 '05 #4

P: n/a
Thanks for the reply.

Drop Table seems to work fine in DAO as well... I tried using ADO in this
mdb, but could not clone a recordset - ADO does not have this functionality
in mdbs... or so it appears...
Not sure why there isn't an Excel 11.0 option. Excel 8 is Excel97, a
major revision and the version from which backwards compatability
commenced. So use Excel 5.0 is for pre-Excel97 workbook formats,
Excel 5.0 for more recent versions.
I assume you mean: "use... Excel 8.0 for more recent versions."

so there is no reason to use Excel 5.0 unless I want to link really old
spreadsheets?

"onedaywhen" <on********@fmail.co.uk> wrote in message
news:b8**************************@posting.google.c om... "deko" <dj****@hotmail.com> wrote in message news:<Lz*******************@newssvr29.news.prodigy .com>...
a couple of follow-up questions:


Replies inline:
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?


Not sure why there isn't an Excel 11.0 option. Excel 8 is Excel97, a
major revision and the version from which backwards compatability
commenced. So use Excel 5.0 is for pre-Excel97 workbook formats,
Excel 5.0 for more recent versions.
is it better to use:
db.Execute "DROP TABLE Sheet1"
--or--
db.TableDefs.Delete "Sheet1"


Dunno, I don't do DAO! DROP TABLE in ADO works for me (clears the rows
and column headers but worksheet remains in the workbook).

--

Nov 12 '05 #5

P: n/a
Cloning an ADO recordset certainly does work for a Jet .mdb database.

Typo! It should have read, "use Excel 5.0 is for pre-Excel97 workbook
formats, Excel 8.0 for more recent versions." Sorry for the confusion.

--

"deko" <dj****@hotmail.com> wrote in message news:<cs**************@newssvr25.news.prodigy.com> ...
Thanks for the reply.

Drop Table seems to work fine in DAO as well... I tried using ADO in this
mdb, but could not clone a recordset - ADO does not have this functionality
in mdbs... or so it appears...
Not sure why there isn't an Excel 11.0 option. Excel 8 is Excel97, a
major revision and the version from which backwards compatability
commenced. So use Excel 5.0 is for pre-Excel97 workbook formats,
Excel 5.0 for more recent versions.


I assume you mean: "use... Excel 8.0 for more recent versions."

so there is no reason to use Excel 5.0 unless I want to link really old
spreadsheets?

"onedaywhen" <on********@fmail.co.uk> wrote in message
news:b8**************************@posting.google.c om...
"deko" <dj****@hotmail.com> wrote in message

news:<Lz*******************@newssvr29.news.prodigy .com>...
a couple of follow-up questions:


Replies inline:
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?


Not sure why there isn't an Excel 11.0 option. Excel 8 is Excel97, a
major revision and the version from which backwards compatability
commenced. So use Excel 5.0 is for pre-Excel97 workbook formats,
Excel 5.0 for more recent versions.
is it better to use:
db.Execute "DROP TABLE Sheet1"
--or--
db.TableDefs.Delete "Sheet1"


Dunno, I don't do DAO! DROP TABLE in ADO works for me (clears the rows
and column headers but worksheet remains in the workbook).

--

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.