RefreshLink error - 3078

[James R.]

Hi,

I'm in the process of converting an Access 97 backend to SQL Server 2005;
I've tried the SSMA for Access with no avail. So, now I've gone through the
painstaking of hand converting the table structure, constraints, keys, etc;
and even got the data over into SQL using a complex SSIS package.

Now, I'm ready to link the backend data. For now, I'm trying the simplest
approach just to get a working copy..so I'll be using a DSN. I've read about
DSN-less and will try it out in future testing sessions.

I've created the DSN and started the linking process and get an error stating:
"Error: 3011 - The Microsoft Jet database engine could not find the
object 'MyTableName'. Make sure the object exists and that you spell its name
and the path name correctly....See Chapter 10 - Troubleshooting, Item 4 for
assistance."

For arguments sake I also upgraded the project to Access 2000 and got
essentially the same error message; just with error # 3078 instead.

I've checked the DSN connection and got successful connection test. I've
double checked the table names in Access file (originally named
dbo_MyTableName...but, I renamed to MyTableName).

I've read countless articles and newsgroups, browsed forums and MS support,
all of which have not once addressed this.

I'm not sure if there is something I am just not seeing or what? I've been
working on this for about a week now (the whole project for about 5 months).

Anyone have any ideas? I'd appreciate any help that you can give me or point
me to! Please help!

Thanks,
James R.

 

[James R.]

Here is the coding that I'm using to call the RefreshLink method.
Declerations were left out; but I know this code works because if I point the
backend of a MDB file I don't get the error....

I only get the error when attempting to connect to an SQL Server DB table!?
Is it possible that the coding needs to be modified somewhat because I'm
trying to connect to SQL?

'********START CODE*********

If MsgBox("Is " & DBPath & " the correct file and location?", vbYesNo,
"Database PATH") = vbYes Then
newPath = ";DATABASE=" & DBPath
Else
GoTo Find_path:
End If
intNumTables = MyDB.TableDefs.Count
varReturn = SysCmd(acSysCmdInitMeter, "Attaching Tables", intNumTables)
For i = 0 To MyDB.TableDefs.Count - 1
Set MyTableDef = MyDB.TableDefs(i)
If MyTableDef.Connect <> "" Then
MyTableDef.Connect = newPath

'!!!This causes error when connect has a value!!!
'Error # 3078 (for Access 2000) # 3011 (for Access 97)
'error states can't find table
MyTableDef.RefreshLink

End If
varReturn = SysCmd(acSysCmdUpdateMeter, i + 1)
Next i
'*******END CODE*******

I appreciate any help you can give me!!

Thanks,
James R.

 

[Peter Yang[MSFT]]

Hello James,

Based on my experience, it seems that you don't have the proper connection
string for the SQL Server connection. You may want to try the code like
following to test the sitaution:


Function testlinktable()
Dim MyDB As DAO.Database, tbl As DAO.TableDef
Set MyDB = CurrentDb()
Set tbl = MyDB.TableDefs("dbo_tbl1")
tbl.Connect = "DRIVER=SQL
Server;SERVER=testsql;DATABASE=mytest;Trusted_Connection=Yes;TABLE=dbo.tbl1"
tbl.RefreshLink

End Function


Please see the following article for more details:


208295 ACC2000: Procedure to Create Data Sources and Relink ODBC Tables
http://support.microsoft.com/default.aspx?scid=kb;EN-US;208295

If you have any update or questions, please feel free to let's know. Thank
you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Alex Dybenko]

Hi,
instead of RefreshLink you have to delete tabledef and create a new one
see: http://www.mvps.org/access/tables/tbl0010.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[James R.]

Peter,

Thank you for your input. I have tried your suggested function for testing
purposes. I created a table in SQL Server called tbl1 (belonging to the dbo
schema), and a table in MS Access 2000 called 'dbo_tbl1'. I am logged in as
the Administrator and when I try to run the function by using 'Call
testlinktable' I get the following error:

Run-time error '3219'
Invalid Operation.

tbl.Connect = "DRIVER=SQL
Server;SERVER=D7P01YD1\SQL2K5_DEV;DATABASE=MAI_SQL2005;Trusted_Connection=Yes;TABLE=dbo.tbl1"

'D7P01YD1' is the name of the laptop (machine name) and 'SQL2K5_DEV' is the
name of the SQL instance running the 'MAI_SQL2005' database.

Any ideas what I could be doing wrong here? It seems the error is thinking
I'm trying to write to a read-only property (which doesn't make sense because
this is a TableDef object, not a recordset)...or it thinks that TableDef
doesn't support user-defined properties.

I'm totally loss on why this error is occurring for such a simple test!
Please help!

Thanks,
James
It then points to the 'tbl.Connect' line of code. I modified the code to the
following (if this might help figuring it out):


--
Knowledge is the first step towards success. Little knowledge creates big
ideas.

Hello James,

Based on my experience, it seems that you don't have the proper connection
string for the SQL Server connection. You may want to try the code like
following to test the sitaution:


Function testlinktable()
Dim MyDB As DAO.Database, tbl As DAO.TableDef
Set MyDB = CurrentDb()
Set tbl = MyDB.TableDefs("dbo_tbl1")
tbl.Connect = "DRIVER=SQL
Server;SERVER=testsql;DATABASE=mytest;Trusted_Connection=Yes;TABLE=dbo.tbl1"
tbl.RefreshLink

End Function


Please see the following article for more details:


208295 ACC2000: Procedure to Create Data Sources and Relink ODBC Tables
http://support.microsoft.com/default.aspx?scid=kb;EN-US;208295

If you have any update or questions, please feel free to let's know. Thank
you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[James R.]

Alex,

Thank you kindly for your response. I will be trying this out this
afternoon..I'll be sure to respond back with positive/negative results I get
from this. I'll keep my fingers crossed.

Thanks,
James
--
Knowledge is the first step towards success. Little knowledge creates big
ideas.

Hi,
instead of RefreshLink you have to delete tabledef and create a new one
see: http://www.mvps.org/access/tables/tbl0010.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Peter Yang[MSFT]]

Hello James,

It seems that MDAC on this machine have some problem. What's the OS of the
machine? Did you have latest service pack installed?

If it's WinXP/2003, you could refer to the following link for some details
on how to reinstall MDAC or repair it
How to Repair MDAC 2.8 Under Windows XP with SP2 Installed
http://www.macropool.com/en/download/mdac_xp_sp2.html

If you have any update, please feel free to let's know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Yang[MSFT]]

Hello James,

I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[James R.]

Peter,

Thank you kindly for your prompt responses. I've tried the link you've
provided below and had no luck. I still get the same error. I am not
attempting to re-install Office 2000 to see if something had gotten
corrupted; I'm afraid that could be the case because I am also running Office
2007 and each time I switch between the two applications I have to wait for a
configuration screen to reload. It get encumbersome to run the latest
software with the older software; but, unfortunately I have no choice as I am
required by employer to use the archaic software and I just can't seem to
give up the latest version for personal taste reasons.

I'll post back with more information as I obtain it.

Thanks,
James

 

[James R.]

Peter,

Here are some interesting notes and results…

First, this was tested on Windows XP w/ SP2 installed. Using Microsoft
Access 97; because that’s the version that we are currently using with our
client machines.

First, I tried just using your code directly (with of course having a table
on the backend linked table called “dbo_tbl1†and a table in SQL database
called “dbo.tbl1â€). When I run the code it returned “run-time error 3219â€.
Then I was thinking; I’m out of practice with Access programming and quite
used to .Net and OOP concepts, but I’m still quite sure from my old Access
and VB 3.0 days that a function needs to return a value…so I changed your
coding to be a sub instead…no avail…got the same error.

So, I tried the link you included and did all of the steps ‘repair MDAC 2.8
Under Windows XP with SP2 Installed’. This did not work either; same error.

So, here’s the interesting part. I remember you were saying that in most
cases, from your experience, the cause of inability to ‘refreshlink’ is
because the DSN is incorrectly configured/installed. So, I thought well…while
I’m in the linked table backend why don’t I just double-click on the table
I’m trying to link and see what happens.

So I did, and it sure enough had showed the data I had in my SQL server
table. I even went a step further and changed some data, deleted some data,
and even added new rows directly from the backend in Access 97 and
immediately went into SQL Server Management Studio and verified that all my
modifications had showed up as I had made while in Access. I then made
changes in SSMS and verified immediately that it showed in the Access Linked
tables.

This, from just being logical..I don’t have experience, would indicate the
DSN is setup correctly…yes? I’m asking this because obviously the more I can
eliminate from being the problem the less I have to figure out what isn’t
working right. So, unless you have other knowledge from experience that would
make this logic illogical I can safely assume the DSN is working and the
problem lays somewhere within the data layer (most likely) or a corrupted
file (high potential there too).
Does any of this give you other ideas to try? I just can’t seem to figure
this out. I do appreciate your help and especially helping me to brush up on
this Access programming, to ensure my logic isn’t somehow warped from using
OOP for so long now.

Thanks,
James

--
Knowledge is the first step towards success. Little knowledge creates big
ideas.

Peter,

Thank you kindly for your prompt responses. I've tried the link you've
provided below and had no luck. I still get the same error. I am not
attempting to re-install Office 2000 to see if something had gotten
corrupted; I'm afraid that could be the case because I am also running Office
2007 and each time I switch between the two applications I have to wait for a
configuration screen to reload. It get encumbersome to run the latest
software with the older software; but, unfortunately I have no choice as I am
required by employer to use the archaic software and I just can't seem to
give up the latest version for personal taste reasons.

I'll post back with more information as I obtain it.

Thanks,
James
--
Knowledge is the first step towards success. Little knowledge creates big
ideas.

Hello James,

I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Yang[MSFT]]

Hello James,

Regarding your quesition, I think DSN is OK since you can connect to SQL
Server properly. I suspect this might be related to Access 97 since it's no
longer supported. You may want to upgrade Access to newer version to test
the situation.

Also, if you have other test machines, you may want to test on them to see
if this is machine specific problem.

Please let's know if you have any update. Thank you for your time.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[James R.]

Peter,
Thanks again for all of your help; I’ve made some progress.

I was looking over the whole way the application is setup and how we are
accessing information. What we have is a frontend file that connects to the
backend file (all in Access database files/forms). So, I got to thinking..why
can’t I connect. So; long story short it dawned on me that I am trying to
‘daisy chain’ the linkage of tables..by having the front-end connect to the
ACCESS backend, then have the backend connect to the SQL server. So, I just
cut out that Access backend and had the frontend tables directly link to the
SQL backend…this works great…especially in Access 2000…..however…

I do still need to try to get the Access 97 (even if it’s unsupported) to
connect in the same manner. Don’t panic now…my next step after getting the
backend over to SQL and working in production is to do a complete redesign of
the front-end. This will be eliminating the Access front-end all together and
going over to a Visual Studio designed front-end (that’s a whole other huge
project that will be an adventure!).

So, back to the Access 97 problem…I got most of the tables to connect; all
except 5 tables. The reason the last 5 tables can’t get linked is their names
are too long. So, I guess my question now becomes is there a way around this
limitation. I know that if the tables are in Access and SQL that the 16.3
naming convention doesn’t apply…it’s only an issue because I’m using the
linking wizard to link my DSN.

I’ve tried your little code block and still get the 3219 error; so, I guess
it would be great if I could somehow rename the SQL table to something
smaller (like 123abc) and link to a small table in Access (like 123abc) and
then just rename the two tables after the link is established. Obviously this
approach doesn’t work directly because the DSN then looses track of where the
table is in SQL (on the backend).

Do you know of a way to go into a linked table and update the table name
it’s pointing to on the backend? I’m afraid it will require the “refreshlinkâ€
method that keeps producing the error. I’d appreciate any suggestions you can
come up with; I understand that Access 97 is no longer supported, and
honestly, that’s why I’m using the newsgroups in hopes to find an answer; as
opposed to using the MSDN forums.

I appreciate you even taking the time to work with me as much as you have
already and appreciate any thoughts or suggestions you can come up with. For
now, I’ll continue working on figuring out why your small code block isn’t
working. I know if I can get it to work then I can easily do the renaming
idea with the appropriate table names that I need.

Thanks for your time and help!
James

--
Knowledge is the first step towards success. Little knowledge creates big
ideas.

Hello James,

Regarding your quesition, I think DSN is OK since you can connect to SQL
Server properly. I suspect this might be related to Access 97 since it's no
longer supported. You may want to upgrade Access to newer version to test
the situation.

Also, if you have other test machines, you may want to test on them to see
if this is machine specific problem.

Please let's know if you have any update. Thank you for your time.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Yang[MSFT]]

Hello James,

Thank you for your reply. I think you could try to create a view with
shorter name for the backend sql tables to see if it could work around this
issue. I don't think rename table to shorter name and then back could work
for this situation.

Also, I found refreshlink might also work for Access 97.

159691 ACC: Procedure to Create Data Sources and Relink ODBC Tables
http://support.microsoft.com/default.aspx?scid=kb;EN-US;159691

Hope this is helpful. I understand that upgrade appliation might be a
painful decision but it usually worth doing this.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[James R.]

Peter,

You actually hit the perfect answer! I was absolutely ecstatic to read your
posting; I don’t know why it didn’t dawn on me to use a view. As soon as I
read the word "view", I already knew that would work! I tried it out this
morning and it sure did work perfectly; I got the last 5 tables linked…and
was very pleased that I did not have to rename the actual tables on the SQL
Server end. This allows me more flexibility, and less chance of breaking
existing code by not having to rename the tables.

I’ve read that RefreshLink is supposed to work with Access 97; and I find
that it does if I keep it linking to another 97 Access backend file. But,
when I try to go through the DSN approach I get that same error as before (#
3219); no idea why either. I’m probably not going to worry any further about
it, at most I might take a few more swings at it just for personal
satisfaction reasons; the main thing is that I know I can do the conversion
manually! Luckily since we are dealing with replicated frontends I just have
to do the re-linking manually on the master file and then I can easily
replicate and distribute the re-linked frontend to all of my users.

I agree that upgrading is painful, I think my employer had felt that they
could avoid it for the past 4+ years and luckily (in my view, no pun
intended) they had reached the maximum file size and decided to bite the
bullet and go through the upgrading process…which is how I got hired on to
this company. I’ve had many discussions with my employer and he has stated
many, many times that he is very pleased I joined them and have presented
plans and roadmaps to address these software issues and to bring them along
in a small steps in a timely manner.

I’m personally excited to get done with the backend, if all testing goes
well, and to proceed on to eliminating the use of Access 97 on the Frontend
as my next project. It is also scary at the same time; but I am sure that
with people like you and the many others that have helped me to understand
how to patch this together, that I will have this project completed in
shorter times than I have currently planned.

If I do figure out what the problem is with Access 97 and using RefreshLink
to link into SQL Server 2005; I will be sure to update this thread so anyone
else whom might see it can more easily figure this out than I've been able
to. Plus, it's nice to have closure when possible.

Thanks again so much for your help and willingness to discuss a discontinued
product; I look forward to discussing much more current products with you and
others in the coming months.

Take care,
James

 

[Peter Yang[MSFT]]

Hello James,

Thank you for your feedback and nice to see you have found a workaround for
the issue currently.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

newsreader so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.