RunSQL method with tables linked to SQL2005 Express

In ACCESS 2003 :
1. The DoCmd.RunSQL method works fine with tables linked (ODBC) to SQL 2000
on a first machine. On this machine, SQL2005 is not installed.
2. On a second machine, with SQL2000 and SQL2005Express installed,
DoCmd.RunSQL fails with tables linked to SQL2000 or SQL2005Express. In both
cases, I get error '3024' 'File C:\Documents and Settings\ ... \dbo.mdb not
found'.
I checked SQL2005 configuration with the SQL2005 configuration tools, and it
seems ok.
Apart from the RunSQL method, the linked tables can be accessed normally.
Any idea ? Thanks in advance.Hi,
My understanding of your issue is that:
Your Acess application failed with tables linked to SQL Server 2000 or SQL
2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
\dbo.mdb not
found'.
If I have misunderstood, please let me know.
I performed a test but unfortunately I didn't reproduce your issue. My test
was based on the following steps:
1. Create an Access database in Acess 2003
2. Create a SQL Server 2000 database to the File DSN
4. Add linked tables from the ODBC File DSN
5. Create a Form in Acess 2003
6. Add code in the click event:
Dim strSQL As String
strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "'
where ProductID=" & Me.comProductId
DoCmd.RunSQL strSQL
MsgBox "OK"
7. Save the database
Firstly, I opened the database file and triggered the click event of the
Form on my local computer with SQL Server 2000 and SQL Server 2005 without
any problem, then I copied the .mdb file to another machine with SQL Server
2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
From your error message, it seems that your dbo.mdb file cannot be found.
Please check that.
Also, could you please tell me more detailed on your scenario? It is better
that you could describe your whole process so that I could better
understand your issue and probably reproduce your issue according to your
steps.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community 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.
========================================
==============|||Charles,
Thanks for your testing and fast reply.
Your scenario is quite similar to mine and I can't see why results are
different.
In fact, it looks like if access to the tables concerned is denied. So I am
suspecting SQL 2005 security. Before going further in tests, I will have a
closer look at SQL 2005 schemas, which I am new in as they did not exist in
SQL 2000.
When I have completed this, I will let you know more.
Thanks to you.
"Charles Wang[MSFT]" wrote:

> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My tes
t
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "
'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Serve
r
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is bette
r
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community 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
.
> ========================================
==============
>
>|||Charles,
Finally, I got it. The issue is this :
1. When, in ACCESS, you create a link to a SQL table which hasn't got a
primary key, a dialog box appears in second, asking for a field unique
identifier. If you do not select a field, the link is however created and yo
u
can access normally to the table from ACCESS. But the Docmd.RunSql method
does not work. What is disturbing in that case is that the table link is
created however.
2. On another hand, if you select a unique identifier while the source table
hasn't got any primary key, it leads to incoherent behaviour such as tables
appearing different in ACCESS from their real content in SQL.
3. My conclusion is : If you have source tables without primary key (which
of course can happen), you have to choose between A/ Not using Docmd.RunSql
(or CurrentDb.Execute) or B/ Having dangerous behaviour in your app.
I would say it sounds more or less buggy.
I think you will understand my pain when I say it is a real waste that
Microsoft is abandonning ADP platform. With ADPs you can directly manipulate
SQL objects in ACCESS, such as tables, views, procs, ... (and what is more:
most often without writing any code). And this trouble could not happen.
I would be very glad to know your feeling about all this.
Once more, many thanks for your help.
"Charles Wang[MSFT]" wrote:

> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My tes
t
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "
'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Serve
r
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is bette
r
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community 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
.
> ========================================
==============
>
>|||Hi Jason,
Thanks for your detailed explanation on this issue which make me totally
understand your issue.
Yes, I reproduced your issue according to your steps. It is indeed an by
design issue.
Please refer to:
276035 PRB: ADOX Creates Read Only Linked Tables to Access Databases
http://support.microsoft.com/defaul...kb;EN-US;276035
If you are very concerned on this issue, I recommend that you give
Microsoft feedback which will be routed to the related team via the link:
http://www.microsoft.com/office/com...ype=suggestion&
lang=en&cr=US&cat=en-us-office&pt=3a4e9862-cdce-4bdc-8664-91038e3eb1e9
Also, ADP is still available in Access 2007
http://technet2.microsoft.com/Offic...a1c-446a-8ff2-2
21769a58ba51033.mspx?mfr=true
For now, you may assign the primary key in your Access database file when
you add the linked table due to no better alternative resolutions on this
issue.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community 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.
========================================
==============|||Charles,
Thanks for your reply. Just one comment about your suggestion of assigning a
field unique identifier to a link, while source table hasn't got a primary
key.
1. Let us consider a SQL table with no primary key :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Mary
2. In ACCESS (.mdb), create an odbc link to this table, selecting empl_Name
as unique identifier. The link is created without any warning.
3. Then, you can have a look at your table in ACCESS. You will see :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Bob
A bit confusing ...|||Hi Jason,
Thanks for your detailed response.
In this case, you can choose the two columns empl_Name and empl_FirstName
as the unique record identifier.
Please feel free to let me know if you have any other questions or
concerns.
Have a great day!
Charles Wang
Microsoft Online Community 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.
========================================
==============|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support|||Charles,
Thanks very much for your interest in my issue.
My context is the following :
1. I have been developing for long with ADPs, doing the "hard job" in SQL
Server, and using ACCESS as front end. I considered this way of doing as
highly productive. My field could be described as the construction of
analytical relational databases fed with data coming from transactional
systems (finance, supply chain, ... ).
2. Unfortunately, Microsoft is leaving ADP platform. As a matter of fact,
you will see ADPs in ACCESS 2007, but just for the purpose of compatibility.
From now, ADPs don't work with SQL 2005 as far as building purpose is
concerned.
3. So, I am going back to ODBC and ADO with ACCESS .mdb. And problems come.
The one I met with DoCmd.RunSql (or CurrentDb.execute) is one amongst many.
For instance, links refreshing is quite an issue. If you have to frequently
create/kill tables in SQL Server, links management gets complicated.
Thanks for your suggestion, about my example, of selecting two fields as
unique identifier. I confess I didn't think of it. But the problem is
elsewhere : when you receive, from transactional systems, data in tables wit
h
no primary key (and this frequently happens), it would not be reasonable to
add a unique identifier when creating odbc link, as you may never be sure
about the existence of duplicates.
With ADPs, everything is more simple : you work with the database "as it
is", without interfering with risky adds. Roughly speaking, "what you see is
what you get". And of course the direct manipulation of SQL objects in
ACCESS, with no code writing, allows fast developments.
4. As a conclusion, I would say it is possible to find a solution, more or
less satisfying, to this RunSQL problem (In fact, the definite solution will
be to avoid this method). But the real fact is that there is galaxy between
ADPs and ODBC/ADO (in ACCESS world of course) ...
What is rather strange is that Microsoft has, for years, presented ADPs as
THE solution for client/server ACCESS apps.
Once again, thanks very much for your help and interest in these questions.
"Charles Wang[MSFT]" wrote:

> Hi,
> I am interested in this issue. Would you mind letting me know the result o
f
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Have a great day!
> Charles Wang
> Microsoft Online Community Support
>|||Hi Jason,
Appreciate your detailed response. I do understand your concerns. Just one
point that you mentioned I cannot understand clearly.
You said, "From now, ADPs don't work with SQL 2005 as far as building
purpose is concerned.". Did you mean that ADPs cannot work with SQL 2005
from now on? As far as I know, both Access 2003 and Access 2007 can work
well with SQL 2005 Express, Standard and Enterprise edtions.
Did you encounter some issue when you use ADP to work with SQL 2005? If so,
please post the issue here and I can assist you on it.
I performed a test with ADP and managed to update a SQL Server 2005 Express
table with primary key. Also, I managed to execute DoCmd.RunSQL on a no
primary key table in ADP:
I created a table in SQL 2005 Express:
CREATE TABLE [dbo].[Employee](
[First_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[Last_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
)
INSERT INTO Employee VALUES('F1','L1','F1 L1')
INSERT INTO Employee VALUES('F2','L2','F2 L2')
INSERT INTO Employee VALUES('F1','L1','F1 L1')
Then in ADP, I managed to run the query:
DoCmd.RunSQL "Update Employee SET Description='FX LX' WHERE First_Name =
'F1'"
For a table with no primary key, I think it is a by design limitation in
Access database (.mdb) and I have submited a feedback for this.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community 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.
========================================
==============