Can MS Access update a Sybase table

[tdrBates]

We would like to update the route and block fields in the Sybase TableA
with route and block fields in MS Access TableB based on the following conditions:

update tableA
set TableA.block = TableB.block, TableA.route = TableB.route
where format(timeOnly(TableA.ts)) between TableB.tmMin and TableB.tmMax
and TableA.ts between '2005-02-02 04:00:00.000' and '2005-02-03 03:59:00.000'

Can we update a Sybase table directly from MS Access table?
We can export Sybase TableA to MS Access database then try to update. But this would take more steps.

Thanks for any suggestions.

TableA - Sybase Table
bus ts route block
8845 2005-02-02 15:30:00.0 4 103
8845 2005-02-02 16:03:00.0 18 103
8845 2005-02-02 16:11:00.0 18 103
8845 2005-02-02 16:29:00.0 4 103
8845 2005-02-02 16:30:00.0 4 103
8845 2005-02-02 17:00:00.0 4 103
8845 2005-02-02 17:10:00.0 4 103
8845 2005-02-02 16:00:00.0 18 103
8845 2005-02-02 15:33:00.0 18 103
8845 2005-02-02 08:30:00.0 1 103
8845 2005-02-02 07:30:00.0 1 103
8845 2005-02-02 09:30:00.0 1 103
8845 2005-02-02 09:09:00.0 1 103
8845 2005-02-02 09:05:00.0 1 103
8845 2005-02-02 09:00:00.0 1 103
8845 2005-02-02 08:00:00.0 1 103
8845 2005-02-02 11:00:00.0 1 103
8845 2005-02-02 07:00:00.0 1 103
8845 2005-02-02 06:30:00.0 1 103
8845 2005-02-02 06:21:00.0 1 103
8845 2005-02-02 06:17:00.0 1 103
8845 2005-02-02 06:00:00.0 1 103
8845 2005-02-02 05:30:00.0 1 103
8845 2005-02-02 05:00:00.0 1 103
8845 2005-02-02 04:37:00.0 1 103
8845 2005-02-02 11:03:00.0 1 103
8845 2005-02-02 11:11:00.0 1 103
8845 2005-02-02 11:30:00.0 1 103
8845 2005-02-02 12:00:00.0 1 103
8845 2005-02-02 12:30:00.0 1 103
8845 2005-02-02 13:00:00.0 1 103




TableB - MS Access Table

Block tmMin tmMax Route
001-01 04:42 14:09 1
001-01 14:35 16:10 854
001-02 04:51 19:53 1
001-03 04:57 14:20 1
001-03 15:07 15:25 4
001-03 15:33 16:26 18
001-03 16:34 17:32 4
001-03 17:40 18:24 18
001-04 04:53 17:52 1
001-05 05:04 13:24 1
001-05 14:35 15:36 862
001-05 16:01 16:01 5
001-05 16:10 17:08 26
001-05 17:13 18:24 5

 

[PHV]

Why not linking an access table to the Sybase TableA ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tdrBates]

Suppose I can link an MS Access TableB to Sybase TableA.
What would be the syntax to update route and block and TableA with route and block data in TableB?

Thanks.

 

[tdrBates]

I created a gfi.mdb and linked to the two Sybase tables I need to update from MS Access 2000. I would like to use the
DoCmd.RunSQL to update the route and block/run in Sybase table with route and run in MS Access table.

Can someone give me an example of how to update the Sybase table with data from MS Access table?

The query should be based on the following conditions

update SybaseTable
set SybaseTable.block = AccessTable.block, SybaseTable.route = AccessTable.route
where format(timeOnly(SybaseTable.ts)) between AccessTable.tmMin and AccessTable.tmMax
and SybaseTable.ts between '2005-02-02 04:00:00.000' and '2005-02-03 03:59:00.000'

 

[PHV]

Your conditions are not explicit.
Take a look at the result of this query:
SELECT SybaseTable.route, AccessTable.route, SybaseTable.block, AccessTable.block, SybaseTable.ts, AccessTable.tmMin, AccessTable.tmMax
FROM SybaseTable, AccessTable
WHERE SybaseTable.ts Between #2005-02-02 04:00:00# And #2005-02-03 03:59:00#
AND Format(SybaseTable.ts, 'hh:nn') Between Format(AccessTable.tmMin, 'hh:nn') And Format(AccessTable.tmMax, 'hh:nn')
ORDER BY 5
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tdrBates]

In MS Access QBE Grid, I cannot get MS Access to recognize
the linked Sybase table timestamp(e.g., #2005-02-02 4:00:00#)

Any suggestions?

 

[PHV]

In table design view, what is the datatype of ts for access ? Text ?
Take a look at the result of this query:
SELECT SybaseTable.route, AccessTable.route, SybaseTable.block, AccessTable.block, SybaseTable.ts, AccessTable.tmMin, AccessTable.tmMax
FROM SybaseTable, AccessTable
WHERE Format(CDate(Mid(SybaseTable.ts,12,8)), 'hh:nn') Between Format(AccessTable.tmMin, 'hh:nn') And Format(AccessTable.tmMax, 'hh:nn')
AND SybaseTable.ts Between '2005-02-02 04:00:00' And '2005-02-03 03:59:00'
ORDER BY 5
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tdrBates]

The ts datatype is date/time. Therefore the query above gives me an data type mismatch. I would like to use the date/time datatype instead of the text datatype, if this possible so that I can perform some date calculations.

 

[tdrBates]

Hold on. I didn't add the CDate function correctly.

 

[PHV]

The ts datatype is date/time
So, what's wrong with my first query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tdrBates]

Still getting datatype mismatch errors.

Any other suggestions.

field 'ts' has a date/time datatype

 

[tdrBates]

I'm sorry. The Sybase table and MS Access table were not joined properly.

Your first query works fine!

Thanks!!!