Access Front-End, SQL Server Back-End... on Vista

[Dustin Ventin]

I've written an application that utilizes an Access front end, and uses
linked tables to connect to a SQL Server back-end for its data. This
application works perfectly on XP or Server 2003, but begins throwing
problems on Windows Vista.

The error I'm encountering occurs ONLY when I attempt to open a recordset of
data from the application, using the typical recordset code:

Set conn = New ADODB.Connection 'Set up connection to database
Set conn = SetDBConnection

Set rsProject = New ADODB.Recordset 'Set up recordset
rsProject.CursorLocation = adUseClient
rsProject.Open "Yadda Yadda;", conn, adOpenStatic, adLockBatchOptimistic
rsProject.MoveFirst

The SetDBConnection function returns a connection (doing it in one place so
I don't have to write the same code over and over again). The mean of that
function is:

With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & strBackEndPath() 'SQL Server Version
.Open
End With

This code works perfect on every OS except Vista. With Vista, the moment
the application tries to open any recordset, it throws the error: "Could not
use ''; file already in use."

Now, I strongly suspect due to the OS specific-ness of this error that what
I'm seeing is an issue with Vista permissions. I've tried running it as an
administrator, setting the properties to run the application in administrator
mode, and even set the compatability to Windows XP SP2. Nothing seems to
work, and I'm curious if anyone has any information on how to stake this
issue in the heart once and for all.

Thank you,

Dustin

 

[Stefan Hoffmann]

hi Dustin,

With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & strBackEndPath() 'SQL Server Version
.Open
End With

This code works perfect on every OS except Vista.

Sure? This is not a connection string for SQL Server. See

http://connectionstrings.com/sql-server-2005

or

http://connectionstrings.com/sql-server-2008

depending on your SQL Server version.


mfG
--> stefan <--

 

[Dustin Ventin]

You're right, my code links the recordset to the Access database, which is
what has all the queries and links to the SQL Server tables. It works
without a hitch with both XP and Windows Server 2003. I considered that the
connection string was just plain wrong, too... but if that were the case, the
application just plain wouldn't work. That's why I'm almost positive what
I'm looking at is OS specific.

 

[Sylvain Lafontaine]

Show us the final result of the connection string as returned by the
SetDBConnection function. As suspect that in your case, you are using a
file connection string or something like that and that file connection
string has not been copied to the Vista machine.

Use a standard connection string and you won't have any problem. Finally, I
don't see the point of using linked tables if you are using ADO.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Dustin Ventin]

The function I have written to provide the connection string uses this
function:

strBackEndPath = CurrentProject.BaseConnectionString

That is what is returned when this is run:

.ConnectionString = "data source= " & strBackEndPath() 'SQL Server Version

So, what this means is that the connectionstring is automatically directed
to wherever the Myriad front-end file is, wherever it may have been installed
to. Is there a better way to do this that I am unaware of?

Thank you very much for all your assistence,

Dustin

 

[Sylvain Lafontaine]

We don't know what's the value of BaseConnectionString. Simply show us the
final result of the ConnectionString that you're using. Also, you don't
access a SQL-Server the same way as you can access an Access' JET MDB or
ACCDB database file.

As it has been suggested in the other posts, use a proper connection string
for SQL-Server and you shouldn't have any problem.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Dustin Ventin]

Perhaps I'm not being clear enough. This connectionstring does not connect
to SQL Server. This connectionstring connects to the MDE file that is the
application front-end. Why? Because the MDE file contains queries not
available on the actual SQL back-end. No, it's not the most efficent or
correct way of doing things, I realize that.

Now, the function BaseConnectionString returns:

"PROVIDER = Microsoft.Jet.OLEDB.4.0;DATA SOURCE="(Path)"; PERSIST SECURITY
INFO = False; Jet OLEDB:System Database=(Path)"

The part that really stymes me is - IF this is a connectionstring issue -
why would Vista have it, and not XP or 2003 Server? People have been
suggesting, from what I can tell, that this error is because my
connectionstring is pointing at something on my machine itself that is NOT
available on other machines. If that is the case, than this still isn't
problem with my connecting to Access instead of SQL Server, it's just a
problem with the connection to Access being incorrect.

Do I understand the situation correctly?

 

[Sylvain Lafontaine]

Oh, you are using an Access MDB (or MDE?) as an intermediary layer! This is
another story, then. In this case, I have no idea what might be going
wrong. From time to time, we see here some posts from people using this
kind of arrangement and who are suddenly hit by a problem like yours but as
far as I can remember, I don't remember of any solution other than to remove
this middle layer.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Dustin Ventin]

My apologies, I'm very sorry I wasn't clearer. Thank you again for the
effort, I really appreciate it. I'm considering trying SageKey's new
Vista-based software, because I've never had a problem using thier software
as a foundation. I'll let everyone know if that works.

 

[Steve Sanford]

We also had a problem with "Vista". (I still do - but that is another story)

In addition to the linking problem, a batch file to do the FE auto-update
wouldn't work. I don't remember the exact solution, but after several hours
(I called a friend for help), it came down which folder we were using and the
permissions for that folder.

Keep searching... it will work...


HTH