Querying MS SQL database in Access

[Guest]

Hello, I'm not sure if I have the right forum, but here is my dilemma: Using
Access 97, I have configured an ODBC DSN to connect to a SQL database on our
network and populate records in the local Access database. These records are
populated as jobs are running on the SQL server. Some records are missing
from the populated database, and despite upgrading the ODBC driver, upgrading
to Jet 4.0 SP8, uninstalling and reinstalling Access 97, and trying TCP
(dynamic port addressing) and then Named Pipes, I cannot get those records
to appear. This only seems to be happening on new PCs we have recently
deployed in our environment (HP P4 2.8GHz desktop PCs). Any ideas? Very much
appreciate it. Thanks.

Regards,

Randy

 

[Guest]

hi,
I have a db that is connected to an ODBC SQL server. and i
am using A97.
I just used file>get exteranal date>link tables.
once linked, the linked tables are more or less like
internal access tables. write queries with them, open the
tables, ect. I can do anything with them except edit data
in the SQL server mainly because i was given the read only
password. if i had been given the read/write password, i
could edit too.
Try this approch to find your missing records.

 

[Duane Hookom]

Are you viewing the SQL records or copying them to one (or more) local
Access mdb files? What is the nature of your "jobs ... running on the SQL
Server"?

 

[Guest]

Hi Duanne, we are just viewing the SQL records. Thw SQL server processes data
overnight (batch jobs), and sometimes during the day. At any given time users
can open this MS Access frontend and see those records (and it is the records
with data that is still being processed [dynamic] on the server that are
missing; records with data already processed [static] are visible). Again,
not all users are experiencing this problem, oddly enough it has only been a
recent batch of new HP P4s that have given us this problem. Thanks.

Regards,

Randy

 

[Duane Hookom]

So, you are suggesting that users on different PC connecting to the same SQL
Server tables are seeing different records? Are you viewing just the tables
or viewing through queries or forms where you might have a filter applied?

--
Duane Hookom
MS Access MVP
--

Hi Duanne, we are just viewing the SQL records. Thw SQL server processes
data
overnight (batch jobs), and sometimes during the day. At any given time
users
can open this MS Access frontend and see those records (and it is the
records
with data that is still being processed [dynamic] on the server that are
missing; records with data already processed [static] are visible). Again,
not all users are experiencing this problem, oddly enough it has only been
a
recent batch of new HP P4s that have given us this problem. Thanks.

Regards,

Randy

hi,
I have a db that is connected to an ODBC SQL server. and i
am using A97.
I just used file>get exteranal date>link tables.
once linked, the linked tables are more or less like
internal access tables. write queries with them, open the
tables, ect. I can do anything with them except edit data
in the SQL server mainly because i was given the read only
password. if i had been given the read/write password, i
could edit too.
Try this approch to find your missing records.

 

[Guest]

Hi Duanne,

I have asked our developer to put it into words what we are having a problem
with, as follows:

"Basically the problem is that when I run a query in Access against a SQL
database I don't get the results I should. The query joins two tables and the
second table may not have an entry in it that matches the first table, but I
still want the record from the first table to come back and it does not. I
run the same query in Query Analyzer, which doesn't use an ODBC, and I get
the correct results."

 

[Duane Hookom]

Ask your developer to provide the SQL view of both queries. There are
differences in SQL syntax and results from Access to SQL Server.