AW: [AccessD] Seconds Missing

Michael Brösdorf michael.broesdorf at web.de
Mon Jul 19 09:40:26 CDT 2004


Yep, that is true! But SQL Server has a problem like this, too:

If you have a view selecting all rows from a table (SELECT * FROM tbl...)
and
you change the column layout of the table, the view does not 'see' those
changes
until you open it in design view , execute it from there and save it.

There is a command or option that tell's SQL Server to update the views in
such cases
(WITH SCHEMABINDING I believe, but am not sure..)

If the view is linked into an Access database, one can have lots of fun
tracking down this
problem...


Michael

-----Ursprüngliche Nachricht-----
Von: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]Im Auftrag von Steve
Conklin (Developer at UltraDNT)
Gesendet: Montag, 19. Juli 2004 16:25
An: 'Access Developers discussion and problem solving'
Betreff: RE: [AccessD] Seconds Missing


You may need to delete the linked table from Access and then re-link it.
I recently had an Access linked table refuse to see a decimal field on
SQL Server as anything but an integer until I delete/relinked.  (I
refreshed the links endless times to no avail).  I think our friend
Access sometimes just won't pick up all the schema info unless you force
it on him!

Hth
Steve




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Martin Caro
Sent: Monday, July 19, 2004 9:52 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Seconds Missing


Michael

No, if I enter a date/time directly into the linked table any seconds eg
:25 are changed to :00 when the record is saved. Yes, if I enter a
date/time directly into the table using SQL Enterprise Manager....
seconds are accepted.

I can hold seconds OK in the Global variable and a local table field.

Your suggestion re converting date/time to a string looks like a
promising solution for me so I will that a try.

Thanks

Martin

----- Original Message -----
From: "Michael Brösdorf" <michael.broesdorf at web.de>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Monday, July 19, 2004 11:17 PM
Subject: AW: [AccessD] Seconds Missing


Does it work if you enter a time including seconds directly into the
linked table?

If so, I would try to track down the problem using the Profiler from SQL
Server. Run the append query while Profiler is running to find out what
exactly is sent to the server.

Comparing date/time-values is always a problem with Access and
SQL-Server, because date and time are always stored as ONE number. I've
come to the point where I explicitly convert any data/time-value to a
string ("20041231", "1755") before I do the comparison. This way you can
also solve the problems that might result from the different base-dates
used in SQL Server and Access.

Michael

-----Ursprüngliche Nachricht-----
Von: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]Im Auftrag von Martin Caro
Gesendet: Montag, 19. Juli 2004 14:45
An: Access Developers discussion and problem solving
Betreff: Re: [AccessD] Seconds Missing


Michael

Using an append query on an ODBC/Linked table.

I need to retrieve the record when the user exits using a composite
UserID and LoginInTime key and insert a logoff time however, the value I
have stored in a global variable has seconds and the value stored in the
table has been rounded to full minutes so I'm never going to retrieve
the correct record unless the login was actually on the full minute.

Martin


----- Original Message -----
From: "Michael Brösdorf" <michael.broesdorf at web.de>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Monday, July 19, 2004 10:21 PM
Subject: AW: [AccessD] Seconds Missing


How exactly are you trying to write the time to sql server (ODBC/Linked
table, ADO, etc.)?

Michael

-----Ursprüngliche Nachricht-----
Von: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]Im Auftrag von Martin Caro
Gesendet: Montag, 19. Juli 2004 13:30
An: Access Developers discussion and problem solving
Betreff: [AccessD] Seconds Missing


Hi Folks

I have spent some time in the archives and reviewing past date/time
issues looking for a fix for this problem: I grab the user login time
using NOW() and save it to an SQL table however any seconds apparent in
Access always end up as :00 being save in my SQL table. I was using
smalldatetime data type which I changed to datetime but without success.
I have played with
Format() a bit but maybe someone knows where my seconds have gone?

Martin

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list