Stuart McLachlan
stuart at lexacorp.com.pg
Wed May 6 06:09:56 CDT 2009
If you are using an FE on a workstation to an SQL Server BE on a server, the MDAC on the server is irrelevant, it's the workstation MDAC that does the work. I doubt that this is MDAC related. I'd suspect a permission problem. Does the server have another instance of SQL Server running on it? Is the Windows Server 2003 running in a domain? Are the SQL Server instances on Vista and Win 2003 set up to used Windows Authentication, SQL Server Authentication or "Mixed mode"? (I don't have access to Win Server 2003 at the moment so can't answer about the latest file versions.) -- Stuart On 6 May 2009 at 19:52, Borge Hansen wrote: > Shamil, > Thanks for your reply. > Yes, MDAC files are now considered part of the operating system, and you > can't readily downgrade or upgrade the version .... > > I still have a mysterious problem. > Please excuse if this is a bit long. > This is causing me great frustration. > I hope someone can be of help! > > My problem relates to following setup: > Access2003 Frontend (FE) on Win2003 Sp2 Server > The FE has been modified to work against a SQL2005 Db Backend. > Some functionality uses linked ODBC tables, some functionality uses ADODB > record sets. > And some uses ADO for executing Stored Procedures (SP)s in the SQL Db. > I have developed code (functions) that send a parameterized SP request to > the SQL Db. > The last parameter is an output parameter - the value of which after the SP > has run is returned to the calling code in the Access aplication / VBA . > This is working without any problems on several systems ... all with the ADO > 2.8 version referenced in the VBA > > The application has been tested to run with no problems in this setting: > > SQL 2005 Express on Vista Ultimate laptop as server in a workgroup setup > Frontend tested on the Vista Ultimate laptop; on a Windows XP laptop > upgraded to WindowsXP SP3 in April 2008; on a Vista Home laptop, and on > another WindowsXP > All works fine! > > However, on the client's site - it does not work. > > The SQL2005Express has been set up in the same way on my Vista Ultimate > laptop and the client's Windows Server 2003 SP2: > > TCP/IP protocol enabled and set to listen on port 9999 rather than dynamic > ports > > I can create ODBC DNS using this port and establish linked tables to the SQL > Db - both on the Windows2003 Server and on the Vista Ultimate .... > > I can run the particular code that returns the output parameter form the > Stored Procedure from any of my workstations against the SQL Db on the Vista > Ultimate laptop (computers in workgroup no domain) > > With the same application running either from a workstation in the > Windows2003 SP2 Server workgroup setup, or directly on the Windows2003 SP > Server machine itself, I have verified that the code issuing the SP request > receives back the output parameter intermittently. Where I would expect "0 - > bla bla" or "1 - bla bla" I get nothing,i.e.: "" > > I believe it may have something to do with the MDAC version installed on the > client's site - on the Windows2003 SP2 > > The SQL2005 Express installed on the Windows2003 SP2 reports the following > propeties: > Microsoft SQL Server Management Studio Express > 9.00.4035.00 > Microsoft Data Access Components (MDAC) > 2000.086.3959.00 (srv03_sp2_rtm.070216-1710) > Microsoft MSXML 2.6 3.0 5.0 6.0 > Microsoft Internet Explorer 7.0.5730.13 > Microsoft .NET Framework 2.0.50727.42 > Operating System 5.2.3790 > > On the Vista Ultimate laptop, the SQL2005 Express reports: > Microsoft SQL Server Management Studio Express > 9.00.4035.00 > Microsoft Data Access Components (MDAC) > 6.0.6000.16386 (vista_rtm.061101-2205) > Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 > Microsoft Internet Explorer 7.0.6000.16575 > Microsoft .NET Framework 2.0.50727.1433 > Operating System 6.0.6000 > > On all machines the ADO DLLs and .tlb files sits in: C:\Program Files\Common > Files\System\ado > > The VBA Code requires a reference to ADO2.8 in order for the return of an > output parameter from a Stored Procedure in SQL Db to work. > > With the Frontend Access2003 application (FE) placed on various machines we > have the following ADO references: > > FE on Vista Ultimate or on a Vista Home workstation laptop: > - ADO2.8 is referenced to a file called msado28.tlb (!!) > - The msado15.dll is versioned as 6.0.6000.16386 > - Function call works OK! > > FE on WindowsXP SP3 against the SQL2005Express on Vista: > - ADO2.8 is referenced to the file called msado15.dll which is versioned as > 2.81.1132.0 (xpsp.080413-0852). > - The ado folder has no msado28.tlb file !! > - Function call works OK > > FE on the Windows2003 Server SP2 against SQL2005Express on same machine: > - ADO2.8 is referenced as msado15.dll which is versioned 2.82.3959.0 > - No msado28.tlb file > - Function call does NOT work... > > So, my questions to the list is: > Is it MDAC related? > If not what else could it be? > How can I debug this further? > Is it the MDAC on the machine where SQL2005Express resides that is important > here or the MDAC on the workstation or both? > What is the latest SP of Windows Server 2003? and what are the version of > the MDAC files on the latest version, especially msado15.dll ?? > ?? > > Regards > borge > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com