topic Re: read data from ms access in Application Development and Automation Discussions

read data from ms access

Former Member — Tue, 29 Sep 2009 18:36:20 GMT

He friends,

I am reading data from ms access data base using abap , when I am selecting the complete table by sql select * from tabl1 it is woring fine . Now I wan to read data from this table for a perticular date or for a specific period . Please tell me the sql statement .date field is date type in ms access table . data in this field is looking as "22/09/2009 10:54:00", Please tell me the suitable sql statement for the same

Regards

Re: read data from ms access

Sandra_Rossi — Tue, 29 Sep 2009 22:34:25 GMT

Maybe the timestamp is in unix time (seconds since 1/1/1970, as for excel).

With your SQL, can't you extract the raw value of any timestamp field

You may try by selecting different intervals (select count(*) from tabl1 where timestamp between 1000000 and 2000000) until you get something.

By the way, I am curious how you can SQL MS-ACCESS from ABAP?

Re: read data from ms access

Former Member — Wed, 30 Sep 2009 10:07:45 GMT

Hi,

within MS-Access the selection for a date field is with '#<date>#'. So you have to change the ABAP-SQL statement. You can check this while creating a testformular with VBA-Code within Access. It's the same behaviour.

The other option is to create a query in Access with a new field: SAPDATE=get_SAPDATE(yourfield)

Within the VBA-function module get_SAPDATE you may create a correct SAP date value and give this back as field type long integer.

For example:

public function get_SAPDATE(mydate as date) as long integer.

on error goto get_sapdate_err

dim strDate as string

get_sapdate = 0

strDate = ""

strdate = format(year(mydate),"0000")

strdate = strdate & format(month(mydate),"00")

strdate = strdate & format(day(mydate),"00")

get_sapdate = clng( strdate)

get_sapdate_err:

get_sapdate = 0

endfunction

regards

Peter

Re: read data from ms access

Former Member — Sat, 10 Oct 2009 12:06:40 GMT

Hi Peter,

Thanks for u reply ,

I am not understanding , can u please explain using date as ( 22/09/2009 10:30:00 ) for example.

Regards

Re: read data from ms access

Former Member — Tue, 13 Oct 2009 10:21:25 GMT

Hi friends ,

I have solved the problem by using date = '#9/14/2009 00:00:00AM#'. it is returning date = 09/14/2009 1:08:00 PM in USA format , Now i want to date as 09/14/2009 13:08:00 in ISO format . Is there any function module to conver this date in iso format please suggest.

Thanks

Regards