WHAT IS LINKING USED FOR IN ACCESS 2000.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created infor from excel and used export to bring it in Access. Now I want
to be able to 'link' them so If I change something in one- it will
automatically change it in the other program. IS THIS WHAT 'LINKING' IS USED
FOR?
 
Hi Angel,

The main idea of linking is to ensure that Access is using only the
current data from the external source, rather than data imported at some
time in the past. With some data sources, it's a two way link: the data
can be changed in Access (and the source is updated accordingly).

The situation regarding linking to Excel data is complicated, but as far
as I can remember Access 2000 will allow you a two-way link. (In the
most recent versions of Access, linked data in worksheets cannot be
updated.)
 
It's really hard to test unless you have a lot of (real or virtual)
PC's around - which I don't.

The 2003 and 2002 service packs seem to make a change to
the Jet IISAM registry settings. MSEXCL40 is replaced with
an Access dll.

AFAIK, only the 2002 and 2003 service packs make this
change, but it may affect 2000 if installed.

So far no-one has responded to my requests for people to look on their own
pc's :~(

(david)
 
I don't have Access 2000 anywhere...

It's really hard to test unless you have a lot of (real or virtual)
PC's around - which I don't.

The 2003 and 2002 service packs seem to make a change to
the Jet IISAM registry settings. MSEXCL40 is replaced with
an Access dll.

AFAIK, only the 2002 and 2003 service packs make this
change, but it may affect 2000 if installed.

So far no-one has responded to my requests for people to look on their own
pc's :~(

(david)
Click to expand...
 
When you look at your own PC, has the msexcl40.dll IISAM
been replaced by MSAexp30.dll at
HKLM\software\microsoft\jet\4.0\engines\excel\win32

do create table queries work?

db.execute "select * into [Excel 8.0;DATABASE=c:\tmp.xls].[fred] from
myTable"

do append queries work?
Db.Execute "INSERT INTO [Excel 8.0;DATABASE=c:\tmp.xls].[a1:b50] SELECT
myTable.* FROM myTable;"

do update queries work?

db.execute "UPDATE [Excel 8.0;DATABASE=c:\tmp.xls].[fred] SET [fred].Field1
= null;"

do linked table update queries work?

db.execute "UPDATE [fred] SET [fred].Field1 = null;"

do linked table append queries work?

db.execute "INSERT INTO fred SELECT fred.* FROM fred;"

Are linked tables updatable and appendable in the Access window?

(david)
 
Hi David,
I haven't installed O2003 SP2 yet on this machine, so still have
msexcl40.dll and functioning update queries.

When you look at your own PC, has the msexcl40.dll IISAM
been replaced by MSAexp30.dll at
HKLM\software\microsoft\jet\4.0\engines\excel\win32

do create table queries work?

db.execute "select * into [Excel 8.0;DATABASE=c:\tmp.xls].[fred] from
myTable"

do append queries work?
Db.Execute "INSERT INTO [Excel 8.0;DATABASE=c:\tmp.xls].[a1:b50] SELECT
myTable.* FROM myTable;"

do update queries work?

db.execute "UPDATE [Excel 8.0;DATABASE=c:\tmp.xls].[fred] SET [fred].Field1
= null;"

do linked table update queries work?

db.execute "UPDATE [fred] SET [fred].Field1 = null;"

do linked table append queries work?

db.execute "INSERT INTO fred SELECT fred.* FROM fred;"

Are linked tables updatable and appendable in the Access window?

(david)

John Nurick said:
I don't have Access 2000 anywhere...
Click to expand...
Click to expand...
 
I have A2000 on my PC (along with 2002 and 2003). I have not installed the
KB and SP2 updates that take away the ability to update linked EXCEL
spreadsheets, so my registry won't tell "any news" about what you ask < g >.

However, based on the information that MS provided when this feature was
eliminated for 2002 and 2003, those "changes" do not affect 2000.
 
On my pc, the update disabled my create table queries, but
I'm told that was just co-incidental, so I'm waiting on somebody
to report that yes, they actually have tried it :~)

(david)

John Nurick said:
Hi David,
I haven't installed O2003 SP2 yet on this machine, so still have
msexcl40.dll and functioning update queries.

When you look at your own PC, has the msexcl40.dll IISAM
been replaced by MSAexp30.dll at
HKLM\software\microsoft\jet\4.0\engines\excel\win32

do create table queries work?

db.execute "select * into [Excel 8.0;DATABASE=c:\tmp.xls].[fred] from
myTable"

do append queries work?
Db.Execute "INSERT INTO [Excel 8.0;DATABASE=c:\tmp.xls].[a1:b50] SELECT
myTable.* FROM myTable;"

do update queries work?

db.execute "UPDATE [Excel 8.0;DATABASE=c:\tmp.xls].[fred] SET [fred].Field1
= null;"

do linked table update queries work?

db.execute "UPDATE [fred] SET [fred].Field1 = null;"

do linked table append queries work?

db.execute "INSERT INTO fred SELECT fred.* FROM fred;"

Are linked tables updatable and appendable in the Access window?

(david)

John Nurick said:
I don't have Access 2000 anywhere...



It's really hard to test unless you have a lot of (real or virtual)
PC's around - which I don't.

The 2003 and 2002 service packs seem to make a change to
the Jet IISAM registry settings. MSEXCL40 is replaced with
an Access dll.

AFAIK, only the 2002 and 2003 service packs make this
change, but it may affect 2000 if installed.

So far no-one has responded to my requests for people to look on their own
pc's :~(

(david)



Hi Angel,

The main idea of linking is to ensure that Access is using only the
current data from the external source, rather than data imported at some
time in the past. With some data sources, it's a two way link: the data
can be changed in Access (and the source is updated accordingly).

The situation regarding linking to Excel data is complicated, but as far
as I can remember Access 2000 will allow you a two-way link. (In the
most recent versions of Access, linked data in worksheets cannot be
updated.)


On Fri, 7 Apr 2006 07:20:01 -0700, Angel

I created infor from excel and used export to bring it in Access.
Click to expand...
Now
I
want
to be able to 'link' them so If I change something in one- it will
automatically change it in the other program. IS THIS WHAT
Click to expand...
'LINKING'
IS
USED
FOR?
Click to expand...
Click to expand...
Click to expand...
 
If I remember correctly, A2K was released under a support regime of
'the last three versions'. In 2002, this was changed to '5 years from
release', which gave MS better risk management on future liabilities.*

Since 2000 is out of support, no patches have been released, and
arguably MS is no longer responsible for 'patent infrigements' in
the product (that should be embarrassing for a company that puts
so much effort into DRM)..

The good side of having a product go out of support is the MS is
less likely to break it with well intentioned fixes like the rounding
change, the vb runtime disaster, or the sp8 'security' update.

However, the change to A2003/A2003 seems to have been made
by a change to the Jet 4.0 Excel IISAM, so whatever effect it
has, it may effect all Jet 4.0 users on the PC - including A2000
and dot net applications. In fact, the big question is if it BREAKS
A2000 and dot net applications in some circumstances. For
example, can A2000 run if Jet is calling an A2K3 dll? Will
dot net applications fail if the A2K3 runtime is uninstalled?

(david)

*Although the record for A2000 shows that in reality they managed
the risk by just refusing the fix the problems.
 
Back
Top