Access 97/SQL 2000 Problem

We have a Access app that has been split with the backend on SQL 2k.

We setup an ODBC connection on the clients to point to the SQL server
and we are using NT Auth to connect to the SQL Server. Its been
working this way for years.

We need to change it up so that the users are not using NT Auth to
connect to the SQL Server and we are running into a problem.

The first couple forms work as they always do, it searchs, pulls
records etc. When we get a little deeper into the Access app there are
some data grids that are loaded with data from the SQL Server. When
these are displayed they are solid white, like they are not getting
any data from the SQL Server. If we close that form and go back into
it, its populated with data. If we close that form, we get an error
about not being able to connect to the ODBC datasource.

Its reproduceable on a consistent basis I just cant figure out why its
doing that. It used to be setup this way (no domain auth) for years
and then we switched to domain auth to make it easier to manage the
users. Now that we are going back to the old way its freaking out.

As a test I built a new XP Pro box that is not part of the domain and
a new SQL 2k box that is not part of the domain and copied the
database to the new SQL box to try a fresh setup with no mods done by
me and its the same thing.

Any ideas?

Re: Access 97/SQL 2000 Problem

Try this on the workstation computer (the computer that does not contain
the Sql Server setup/database)

Load sql server tools on the workstation -- i.e. enterprise manager and
query analyzer (well, it will load automatically). Then see if you can
connect to your server (make sure your network cords are all connected,
hubs/switches are all in order...

Now try setting up a new sql server registration from enterprise manager
(on the workstation computer). Try windows auth, if that doesn't get
it, then on the server computer create a user account in enterprise
manager (security section) and then try creating a sql server
registration on the workstation computer enterprise manager using the
sql server account. Then go to query analyzer to see if you can
connect.

BTW, what server are you using? If it is anything before win2000
server, then your easiest fix (and really the cheapest) is to upgrade
from NT4 to at least win2k or higher (ideally win2003 server).

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Access 97/SQL 2000 Problem

On Oct 24, 10:31 am, Rich P wrote:
> Try this on the workstation computer (the computer that does not contain
> the Sql Server setup/database)
>
> Load sql server tools on the workstation -- i.e. enterprise manager and
> query analyzer (well, it will load automatically). Then see if you can
> connect to your server (make sure your network cords are all connected,
> hubs/switches are all in order...
>
> Now try setting up a new sql server registration from enterprise manager
> (on the workstation computer). Try windows auth, if that doesn't get
> it, then on the server computer create a user account in enterprise
> manager (security section) and then try creating a sql server
> registration on the workstation computer enterprise manager using the
> sql server account. Then go to query analyzer to see if you can
> connect.
>
> BTW, what server are you using? If it is anything before win2000
> server, then your easiest fix (and really the cheapest) is to upgrade
> from NT4 to at least win2k or higher (ideally win2003 server).
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

I know its connecting because the ODBC setup of the System DSN tests
successfully, and the initial Access 97 form that loads has an option
to do a search and I can search just fine through the SQL Server. Its
returns records just fine on those forms.

The problems seems to be limited to datagrids and they work if you go
into the form that contains them, come up blank, back out, then go
back into the form with the data grid.

--Mike

Re: Access 97/SQL 2000 Problem

On Oct 24, 8:46 am, m...@mcarlson.net wrote:
> On Oct 24, 10:31 am, Rich P wrote:
>
>
>
>
>
> > Try this on the workstation computer (the computer that does not contain
> > the Sql Server setup/database)
>
> > Load sql server tools on the workstation -- i.e. enterprise manager and
> > query analyzer (well, it will load automatically). Then see if you can
> > connect to your server (make sure your network cords are all connected,
> > hubs/switches are all in order...
>
> > Now try setting up a new sql server registration from enterprise manager
> > (on the workstation computer). Try windows auth, if that doesn't get
> > it, then on the server computer create a user account in enterprise
> > manager (security section) and then try creating a sql server
> > registration on the workstation computer enterprise manager using the
> > sql server account. Then go to query analyzer to see if you can
> > connect.
>
> > BTW, what server are you using? If it is anything before win2000
> > server, then your easiest fix (and really the cheapest) is to upgrade
> > from NT4 to at least win2k or higher (ideally win2003 server).
>
> > Rich
>
> > *** Sent via Developersdexhttp://www.developersdex.com***
>
> I know its connecting because the ODBC setup of the System DSN tests
> successfully, and the initial Access 97 form that loads has an option
> to do a search and I can search just fine through the SQL Server. Its
> returns records just fine on those forms.
>
> The problems seems to be limited to datagrids and they work if you go
> into the form that contains them, come up blank, back out, then go
> back into the form with the data grid.
>
> --Mike- Hide quoted text -
>
> - Show quoted text -

Mike,
Do the data grids have their own authentication credentials - perhaps
writen in code behind the form?
P