accessing data in table, alternative ways

[davikokar]

Hallo,

I was wondering if there is an alternative way of accessing the data in a table, va vba code.

The code I normally use is similar to this:

   Set m_db = CurrentDb()
   Dim rs As Recordset
   Dim sql As String
   sql = "SELECT tblTest.when & tblTest.description FROM tblTest;"
    Set rs = m_db.OpenRecordset(sql, dbOpenForwardOnly)

Is there something else then recordset to store data? Or another way to create one ?

Thanks for suggestions

 

[MazeWorX]

The recordset can be a stored query or even a table if you want multiple records then use recordset if its a single record a DLookup can be used. Depends on what you want to do determines the method.

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[Golom]

Generally speaking ... SQL is the only mechanism used to retrieve or manipulate data in a relational database. The various data access protocols (e.g. DAO, ADO) provide only the recordset object to make such data available in a programming environment.

(Caveat: ADO has a "Record" object but that depends ultimately on the same ideas as a conventional recordset.)

Things like DMax, DMin, DLookup, DCount, etc. are just wrappers that create and run SQL, retrieve the generated data into a recordset and return the result to the point of invocation.

If you can describe the programming issue that prompted this question we may be able to offer some insight.

 

[davikokar]

thanks for the answers.

The programming issue is this:

If I build a recordset with this sql "SELECT tblTest.when & tblTest.description FROM tblTest;" (note the "&" concat symbol) and the description field is a memo field longer then 255 characters. Then the result is that in my recordset I can see only the first 255 characters of the concatenated field.

 

[Golom]

How are you determining that?

Note that some of the display controls impose a limit on the number of characters they can support.

I have run some code similar to yours and dropped the returned concatenated field into a VB string variable. The length of that returned string was (in my case) 3702 characters.

However, when I used the recordset as the DataSource for a FlexGrid the cell contents were truncated to 255. I didn't check but other controls (e.g. DataGrid, dbGrid) may have similar limits.

Just to check out the possibility

Dim db as DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String

Set db = Currentdb()

SQL = "SELECT tblTest.when & tblTest.description As [Concat] FROM tblTest;" 

Set rs = db.OpenRecordset(SQL)

Msgbox Len(rs![Concat])

 

[davikokar]

yes the Len function will return the correct value.

But if I try to print (debug.Print, for example) I get 255 correct characters and then something like:

?????????
 ????????? ??
????4??????????t + ? ???ÿ
l ? ? ??$ ???? ? ????? ?????! ?? ¸??"?A??]??"? ??????????????????9 ??????? ????
0 ?????h ?? 0 ?? ???????????????? ??????????? ??0 ????????D ?%??? %??? ??? ? ?
?

?!?   ? ?! ???% ?? ?e?„?
????%??%? ?? ?%????e????%???!? ?????
?
?   ?????% ????e? ?????%??%? ?? ?%????e????%???!? ???? ?? ???? ? ?%???% ? ?? ?%?????????%???%? ????? ?! ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? ?? ???
??? ? ? ? ? ???"? ???  ? ??
??????? ? ? ????????
? ?!
????%? ? ?
?
??? ??%?! ???
??? ???
? ???? ?? Ð ?
???? ???
1  ???????? 5 ?????

 

[Golom]

Allen Browne has identified this as a BUG.

I have found that you can get the concatenated result cleanly in code with

Dom LongString As String
sql = "SELECT tblTest.when, tblTest.description FROM tblTest;"
Set rs = m_db.OpenRecordset(sql, dbOpenForwardOnly)

LongString = rs![When] & rs![description]

Debug.Print Longstring

That may not help all that much but it does (sort of) work around the bug.

You could also try Allen's "union with a memo field" idea if you can handle it as a read-only recordset.

 

[davikokar]

aha, this is interesting information. Thanks.

Unfortunately in my project I must use queries that are designed by others. So I cannot write my own SQL code and my own queries. So I have to handle the situation with the concat.

I noticed that the DLookUp function is working correctly. But I don't know how to use it to loop through a recordset and return all value. Is there a way ?

 

[Golom]

Any workaround that comes to mind involves writing some SQL and you are not allowed to do that!

With the caveat that I know nothing about the situation in which you find yourself, is there any possibility that the "others" can be pursuaded that their query does not work because of a bug in Access and they need to provide an alternative?