473,695 Members | 2,043 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Updating Linked Table - why is action query failing?

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("Exc elTx", "tblOutput" ), 0)
Set db = CurrentDb()
db.TableDefs.De lete "Sheet1"
Set tdf = db.CreateTableD ef("Sheet1")
strLink = "Excel 5.0;DATABASE=" & strXlTx
tdf.Connect = strLink
tdf.SourceTable Name = "Sheet1$"
db.TableDefs.Ap pend 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
5 4475
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******* ***********@new ssvr29.news.pro digy.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("Exc elTx", "tblOutput" ), 0)
Set db = CurrentDb()
db.TableDefs.De lete "Sheet1"
Set tdf = db.CreateTableD ef("Sheet1")
strLink = "Excel 5.0;DATABASE=" & strXlTx
tdf.Connect = strLink
tdf.SourceTable Name = "Sheet1$"
db.TableDefs.Ap pend 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
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.De lete "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("Exc elTx", "tblOutput" ), 0)
Set tdf = db.CreateTableD ef("Sheet1")
strLink = "Excel 8.0;DATABASE=" & strXlTx
tdf.Connect = strLink
tdf.SourceTable Name = "Sheet1$"
db.TableDefs.Ap pend tdf
'create tblExcelTx
Set tdf = db.CreateTableD ef("tblExcelTx" )
With tdf
.Fields.Append .CreateField("T xDate", dbDate)
.Fields.Append .CreateField("A mount", dbCurrency)
.Fields.Append .CreateField("C omment", dbText, 200)
.Fields("Commen t").AllowZeroLe ngth = True
.Fields.Append .CreateField("E ntity_ID", dbLong)
.Fields.Append .CreateField("T xAcct_ID", dbLong)
.Fields.Append .CreateField("T xType_ID", dbLong)
.Fields.Append .CreateField("P ayment_ID", dbLong)
.Fields.Append .CreateField("E tx_ID", dbLong)
.Fields("Etx_ID ").Attribut es = dbAutoIncrField
End With
Set idxPk = tdf.CreateIndex ("Etx_ID")
idxPk.Primary = True
idxPk.Unique = True
Set idxFld = idxPk.CreateFie ld("Etx_ID")
idxPk.Fields.Ap pend idxFld 'add field to Fileds collection of index
object
tdf.Indexes.App end idxPk 'add index to index collection
db.TableDefs.Ap pend tdf 'append tdf to TablsDefs collection
'append into tblExcelTx all records from Sheet1
DoCmd.SetWarnin gs False
DoCmd.OpenQuery "qryTxAppen dXl"
DoCmd.SetWarnin gs True
'set recordsource on frmTxAssign
Form_frmTxAssig n.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********@fma il.co.uk> wrote in message
news:b8******** *************** ***@posting.goo gle.com...
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******* ***********@new ssvr29.news.pro digy.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("Exc elTx", "tblOutput" ), 0)
Set db = CurrentDb()
db.TableDefs.De lete "Sheet1"
Set tdf = db.CreateTableD ef("Sheet1")
strLink = "Excel 5.0;DATABASE=" & strXlTx
tdf.Connect = strLink
tdf.SourceTable Name = "Sheet1$"
db.TableDefs.Ap pend 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
"deko" <dj****@hotmail .com> wrote in message news:<Lz******* ************@ne wssvr29.news.pr odigy.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.De lete "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
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********@fma il.co.uk> wrote in message
news:b8******** *************** ***@posting.goo gle.com... "deko" <dj****@hotmail .com> wrote in message news:<Lz******* ************@ne wssvr29.news.pr odigy.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.De lete "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
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******* *******@newssvr 25.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********@fma il.co.uk> wrote in message
news:b8******** *************** ***@posting.goo gle.com...
"deko" <dj****@hotmail .com> wrote in message

news:<Lz******* ************@ne wssvr29.news.pr odigy.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.De lete "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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2578
by: Old Timer | last post by:
I wish to type in a number in my "Code" field, for instance 1060, I then wish the number 1060 to trigger an event that will fill in the next field (township field) For instance, 1060 brings up and fills in name "Spencer Tp". I have 30 townships,2 cities, 6 villages and identifiers consisting 38 separate code numbers assigned by our county government to identify each township,city and village. I ran a "Make Table Query", have created a...
1
1864
by: Chris Jackson | last post by:
I'm a novice Access user and am not sure how to solve the following problem. Any help with the following would be greatly appreciated! I have two tables with identical structures, the first holds the data input for a questionnaire, the second holds the scores from the questionnaire, to move the scores I've set up 2 action queries, the first appends the record to the second table, the second action query 'updates' the results by...
4
2275
by: Laura | last post by:
Here's the situation: I'm trying to use an update query to copy data from one row to another. Here is the situation: I have 5 companies that are linked to each other. I need to show all 5 companies on a form. 3 of the companies have common employees. I have a table that looks like this:
1
2051
by: prime80 | last post by:
I'm building a project database to keep track of the various engineering projects ongoing in our department. These projects will be grouped by priority (=High, Medium, Low) and ranked within the priority (=1,2,3,etc.). What I'd like for it to do is automatically update rankings when a project rank is changed, a new project comes in, or a project is completed. For example, let's say there are 4 high priority projects. If High #2 is...
4
8122
by: Wayne Wengert | last post by:
I am trying to create a VB.NET Windows application to move some data from a local Access DB table to a table in a SQL Server. The approach I am trying is to open an OLEDB connection to the local Access DB and then add a Linked Table pointing to the table on the SQL Server and then run an "Insert Into (linked table)" query to add the new rows. I am having a problem getting the syntax to add that linked table to my local Access DB. When I...
1
1510
by: cover | last post by:
I'm trying to put together a system that upgrades records in a database and apparently have run into a bit of a glitch. I think the problem is with the $HTTP_POST_VARS portion of the code. Is there some variable that has to be set for this to work properly or what? Ideas anyone? TIA The Input form: (1 of 3 forms) <html>
4
1579
by: bobh | last post by:
Hi All, In AccessXP I create an Access table then create an update query which updates a field in that table with data from an SQLServer table and when I run it I get the message 'operation must use an updateable query' here is the sql of the query
7
4771
JodiPhillips
by: JodiPhillips | last post by:
Hi, My first post! I've basically taught myself Access and the little I know about VBA through reading these forums and a couple of books. I'm in the middle of a project at work - to put it mildly I've been thrown in at the deep end, in the past I've just done simple databases and that's been fine. Now, however I have to "roll out" various front ends to a single back end for initially 10 users (and the bosses have plans for this to go to...
3
1792
by: Spoogledrummer | last post by:
Hi it's me again, still working on the sam 5 minute problem so feeling kind of thick now. I've dumped the idea of using a textarea for now and am using a textbox instead but am struggling when it comes to updating the database, I've tried several different methods and the one I'm currently battling with is as follows: % set objconn=server.CreateObject("ADODB.connection") objconn.Mode=3 objconn.Open ("DSN=localserver; User ID=blah;...
0
8568
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9115
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8845
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8825
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6491
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5839
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4579
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3003
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2272
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.