Define a VBA Recordset as String

Question

When we define a record set in VBA & read the data from database, recordset converts its datatype to Table's column data type.

Dim rs as adobb.RecordSet 
Set rs = CmdSqlData.Execute() ' After this rs fields will be stored based on table's datatype

Is there any way to set the recordset itself to String and Perform,

Set rs = CmdSqlData.Execute()

For Eg: if there is a timestamp value(which has integer value) in Database table definition, Recordsset sets its field datatype to timestamp.

The problem is , In database,

Time value is 12345 (Not date & time) but when record set reads it, it comes as For Eg: 23-06-2012 10:15:23

I wanted the value as 12345

Why not answer my question, which database? http://stackoverflow.com/questions/11153073/how-to-read-recordset-as-string-in-vba/11153860#11153860 — Fionnuala, Jun 22 '12 at 14:32
You will need to adjust the sql of your recordset in keeping with the database you are using. — Fionnuala, Jun 22 '12 at 14:35
@Remou : Its not taking it from SQL, it is taking it from Tables Definition. Because, how can be the value 12345 be considered as Timestamp in VBA ? — logan, Jun 22 '12 at 14:39
All times and dates in MS Office are numbers. Type in a number into a cell then format it as a date. — Fionnuala, Jun 22 '12 at 14:43
The problem is i can not do string conversion in Query itself because i may not know which column is date or string. Is there any method to select sql as string for all columns ? — logan, Jun 22 '12 at 14:46
OR is there any other method otherthan Recordset for retrieving data from SQL databases — logan, Jun 22 '12 at 14:49
Why don't you just convert the recordset value back to its numeric representation? You say you can't do the conversion in your SQL, so that's your only remaining option. If you really need numbers in your recordset and not Dates, then do an initial select with a clause which will return no records (eg "select * from tablename where 1=2"): you can look at the names and the types of the fields returned and create a select statement which includes the required cast() conversions for any Date columns. — Tim Williams, Jun 22 '12 at 16:24
its a complex process And there are numerous data type is available . . For eg time(6) format And user specified formats . . Looking for simple process for reading the data as in database . . — logan, Jun 22 '12 at 17:19

Rob Paller · Answer 1 · 2012-06-25T17:48:11.597

2

Your SELECT statement will need to perform an explicit CAST for the Teradata TIMESTAMP to FLOAT (DATE is INTEGER)

SELECT CURRENT_TIMESTAMP()
     , CAST(CURRENT_TIMESTAMP() AS FLOAT) AS NumericTimeStamp
;

The rest of the VBA you should be able to work out as the Recordset will recognized the NumericTimeStamp as a FLOAT and not a TIMESTAMP.

Edit

The following SELECT statement will take an integer with a FORMAT clause and return a character data type as a result of the double cast:

SELECT CAST(CAST(1234 as INTEGER FORMAT '99:99:99') AS CHAR(8));

Have you tried to open your recordset with a SELECT statement that you explicitly CAST the column containing the time as a CHAR(8)?

edited Jun 25 '12 at 17:48

answered Jun 22 '12 at 18:43

Rob Paller

7,736
29
26

The problem is , In database, Time value is 12345 (Not date & time) but when record set reads it, it comes as For Eg: 23-06-2012 10:15:23 – logan Jun 23 '12 at 03:54
Two questions: What is the data type on the table definition and what is the FORMAT clause on the table definition? – Rob Paller Jun 25 '12 at 02:05
Field as Integer Format 99:99:99 I managed to convert this timestamp alone. There are number of timestamp format available. I can not automate the SQL query to convert from different datatype to string. I require VBA to read the TERADATA VALUE as string – logan Jun 25 '12 at 14:48

score 1 · Accepted Answer · answered Jun 25 '12 at 14:54

I managed to convert this timestamp alone for now. But There are number of timestamp format available. I can not automate the SQL query to convert from different datatype to string. I require VBA to read the TERADATA VALUE as string What i did ,

    If Field_format = "99:99:99" then
    Sql = "Select Cast(Field_format as integer) from Mytable"
    Elseif Field_format = "99:99:99.999" then
    Sql = "Select Cast(Field_format as Float) from Mytable"
    Elseif Field_format = "99:99:99.999" then
    Sql = "Select Field_format from Mytable"
    End if
...
Set rs = CmdSqlData.Execute()

Kindly note there are so many datatypes available in teradata (Around 400+) , i can not convert everthying as above.

I just wanted to VBA to read Teradata values as string. Wondering There is no VBA Expert in StackOverflow

score 0 · Answer 3 · edited May 23 '17 at 11:56

0

If you're connecting to a SQL Server database, the timestamp datatype is a binary field used to determine if the record has changed or not (see How to convert SQL Server's timestamp column to datetime format) . This is different than an datetime field which holds, as it says, times and dates.

Returning a string from a recordset in ADO would like something like:

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim strSQL as string
Dim strSomething as string

strSQL = "SELECT fld1 FROM tbl1"

Set cn = New ADODB.Connetion
cn.ConnectionString = {your connection string here}
cn.open
rs.open strSQL, cn
While Not rs.EOF
    strSomething = rs!fld1
Wend
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

edited May 23 '17 at 11:56

Community

1
1

answered Jun 22 '12 at 14:58

KFleschner

499
3
13

what if the fld1 is a time stamp numeric value in database ? Will the strsomething be having same numbers ? What my problem is while reading itself record sets converting in to date And time value . . – logan Jun 22 '12 at 17:04
By declaring the strSomething as a string, VBA should handle it exactly as it comes out of the recordset. If it's still coming out as a date, and you need to convert it to some number, try something like CDbl(# your date #)/ – KFleschner Jun 22 '12 at 17:42
this is not worked. As i already said,In Database value is number(as timestamp) but rs!fld1 is a timestamp value. you can not put it into string. – logan Jun 25 '12 at 14:45

Define a VBA Recordset as String

3 Answers3