|
|
You're in the
sql server odbc
group:RunSQL method with tables linked to SQL2005 Express
sql server odbc:
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.
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. [quoted text, click to view] "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 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,
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 you 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. [quoted text, click to view] "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 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. > ====================================================== > >
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/default.aspx?scid=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/community/en-us/wizard.mspx?type=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/Office/en-us/library/1dce641e-ba1c-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 with 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. [quoted text, click to view] "Charles Wang[MSFT]" wrote:
> 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 >
Unfortunately, there is no doubt about the future of ADPs. Microsoft's
message is clear about this. You can have a look at this link I found in these newsgroups. http://www.databaseadvisors.com/gazette/sqlexpress.htm From now, you cannot create tables, views, procs in SQL2005 from ACCESS 2003 ..adp, as it was the case with SQL2000. Of course, you can create these objects in SQL 2005 and use them in ACCESS.adp. But this limitation is just the first sign of the fact that developments will not continue on ADP platform. So it would not be reasonable to invest today on application development based on ADPs. By the way, it is Microsoft's recommendation not to do so. But I am still convinced of the superiority of ADP platform. Thanks to you.
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 NULL, [Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) 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. ======================================================
Charles,
That's really good news. I am very surprised because I had this information from many sides. All I read on the subject in these newsgroups was annoucing the end of ADPs. I'll have to find out where I read that Microsoft recommends MDBs instead of ADPs for future devs. Thanks for the link to Office2007 beta2. I always hesitate to install betas, because of potential troubles, but I will do it on an isolated machine. Anyway, thanks for this great news. [quoted text, click to view] "Charles Wang[MSFT]" wrote:
> Hi Jason, > Thanks for the link. > > I am afraid that the saying is not true. Until now I have not found any > Microsoft public documents talk about that, what is more, Access 2007 can > do that. > Yes, Access 2003 cannot create tables, views, stored procedures of SQL > Server 2005 because its released date is earlier than SQL Server 2005. > However in Access 2007, you will find that it is powerful to manage a SQL > Server 2005 database. You can create a table, view and stored procedures of > SQL Server 2005 by using Access Database Project. I performed a test by > using Access 2007 beta 2 and managed to create tables, views and SPs of SQL > Server 2005 Enterprise Edition without any problem. > > Also, Access 2007 integrated many new features on data import/export. > > I recommend that you download Office 2007 Beta 2 edition for experience: > Download 2007 Microsoft Office System Beta 2 > http://www.microsoft.com/office/preview/beta/download/en/default.mspx > > 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. > ====================================================== > > > > > >
Hi Jason,
Thanks for the link. I am afraid that the saying is not true. Until now I have not found any Microsoft public documents talk about that, what is more, Access 2007 can do that. Yes, Access 2003 cannot create tables, views, stored procedures of SQL Server 2005 because its released date is earlier than SQL Server 2005. However in Access 2007, you will find that it is powerful to manage a SQL Server 2005 database. You can create a table, view and stored procedures of SQL Server 2005 by using Access Database Project. I performed a test by using Access 2007 beta 2 and managed to create tables, views and SPs of SQL Server 2005 Enterprise Edition without any problem. Also, Access 2007 integrated many new features on data import/export. I recommend that you download Office 2007 Beta 2 edition for experience: Download 2007 Microsoft Office System Beta 2 http://www.microsoft.com/office/preview/beta/download/en/default.mspx 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. ======================================================
Hi Jason,
Appreciate your updating and response. That is my responsibility to broadcast good news of Microsoft products. :-) Please try it and let me know if the suggestions are helpful to you at your convenience. If you have any other questions or concerns, please feel free to let me know. It is my pleasure to be of assistance. Have a great day! Charles Wang Microsoft Online Community Support
Hi Jason,
I am responsible for checking this issue status. 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
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |