Datasheet column header error with ADODB

[Scott_Hall]

Morning all,

Having an interesting error I just can't seem to resolve.

I have a datasheet that uses a disconnected ADO recordset - it's a master list of projects and my managers wanted columns that represented data in differing structures, so using a standard query as a source couldn't be done.

The form works just fine...its fast and it loads all data perfectly but...
attempting to use any of the standard datasheet column header options (filter, search, etc) triggers an error:

Syntax error in query. Incomplete query clause.

Has anyone enountered this and is there a fix or workaround? My users are going to revolt if they can't sort and filter from that spot.

Thank you,
Scott

 

[arnelgp]

See ans #1 on this thread
https://stackoverflow.com/questions...manipulate-an-access-datasheet-using-vba-code

 

[Scott_Hall]

Thanks for the very quick answer.

This is a bit of a head scratcher now.

Redesign of the form and recordset (this is a big job - lots of complex data), or proceed with the column functions broken. :banghead:

 

[Mark_]

it's a master list of projects and my managers wanted columns that represented data in differing structures, so using a standard query as a source couldn't be done.

How are you able to build it with ADO but can't build a query?

I am guessing that, if you put your mind to it you could build sub-queries FIRST that standardize your returns from the "columns that represented data in differing structures" in a standardized way, then make one query to run off the sub-queries.

While you won't be able to edit the sheet directly, you should be able to call up a standardized interface to edit each set of related records together.

 

[Pat Hartman]

You could build a stored procedure and use a pass through query to run the sp. Bind the form to the pass through query. That provides a normal DAO recordset which the form can work with. It will of course not be updateable but I don't think you are looking for that.

 

[Scott_Hall]

You could build a stored procedure and use a pass through query to run the sp. Bind the form to the pass through query. That provides a normal DAO recordset which the form can work with. It will of course not be updateable but I don't think you are looking for that.

You know, in all of these years I've never done a passthrough. This sounds interesting...the records do not need to be updateable.

Setting up a passthrough seems to be based around SQL connection strings. How is that reconciled with a virtual adodb recordset? Right now my FORM_OPEN event creates the recordset, then FORM_LOAD populates it with existing data.

 

[CJ_London]

attempting to use any of the standard datasheet column header options (filter, search, etc) triggers an error:

I suspect because the standard options are for DAO - and are different to ADO.

A while since I've looked at it and it may have changed but I think the only ado filters that work are = and begins with. Ado doesn't like an initial * and you need to filter/sort the recordset, not the form.

You can write your own right click menu to achieve this

some code to create the sort options is along these lines - add whatever you decide to call the barname to the control shortcut menu bar property so it will replace the standard menu bar

        Set newBar = CommandBars.Add(BarName, msoBarPopup, False, False)
 
        'newBar.Controls.Add Type:=msoControlButton, id:=210    'SortUp
        Set newItem = newBar.Controls.Add(msoControlButton)
        newItem.Caption = "Sort Ascending"
        newItem.OnAction = "=adoSort(-1)"
        newItem.Style = msoButtonCaption
        
        'newBar.Controls.Add Type:=msoControlButton, id:=211    'SortDown
        Set newItem = newBar.Controls.Add(msoControlButton)
        newItem.Caption = "Sort Descending"
        newItem.OnAction = "=adoSort(0)"
        newItem.Style = msoButtonCaption
 
        Set newItem = Nothing
        Set newBar = Nothing

and the sort function is

Public Function adoSort(UpDown As Integer)
    If Set_Screen_ActiveSubformControl Then
        Screen_ActiveSubformControl.Form.Recordset.Sort = IIf(UpDown = 1, "", "[" & Screen.ActiveControl.ControlSource & "]" & IIf(UpDown = 0, " desc", ""))
        Set Screen_ActiveSubformControl.Form.Recordset = Screen_ActiveSubformControl.Form.Recordset
    End If
End Function

The above is from a bigger piece of code which I'm not in a position to share in its entirety but contains various calls to other functions so you will need to adapt to your requirements

 

[Galaxiom]

A while since I've looked at it and it may have changed but I think the only ado filters that work are = and begins with. Ado doesn't like an initial * and you need to filter/sort the recordset, not the form.

Another catch with older versions of ADO is that only a single operator type could be used in a filter. So, for example, you couldn't have one field equal to something and another field using greater than in the same filter.

This was an issue up to ADO 2.7 but was changed in ADO 6.1.

 

[MajP]

I suspect because the standard options are for DAO - and are different to ADO

I do not think it is ADO versus DAO because you can test this with a standard ADO recordset. It has to do specifically with being a disconnected recordset. I tested this by building an in memory disconnected ADO recordset and a general ADO recordset. With the general ADO it works fine. My thought was that the column controls are forcing a requery which obviously you cannot do with a disconnected recordset. But I do not think that is really what is happening because a general ADO recordset works with the column controls, but if you try to requery using the refresh it fails with a provider not found error. Everything I have read this is a limitation of using a disconnected recordset, I do not think it is an error.

@
Scott_Hall,
I agree doing this on the server side will make the most sense. However out of curiosity, since you are creating the disconnected recordset and then pulling data to populate it, could you have just made a temp table and populated that instead? What was the advantage of the disconnected recordset over having a predefined table?

 

[Pat Hartman]

How is that reconciled with a virtual adodb recordset?

It is NOT an adodb virtual recordset since obviously that isn't working for you.

Pass through queries are queries written in the target language of the server. Therefore, you would use T-SQL. You said your process was too complicated to do as a simple query and that is why I suggested creating a stored procedure. As long as your stored procedure returns only a single recordset, you will not have any trouble executing it with a pass through query. Since pass-through queries are the language of the server rather than Access SQL, the QBE will switch to SQL view when you tell it you want to create a pass-through query and you just type whatever you want. When you save the querydef, you can create a connection string.

In Access, querydefs and tables are interchangeable for most purposes. That is why you can't have a querydef that is the same name as a table. You can call a form tblPerson and a report as tblPerson and a table as tblPerson but you will get a duplicate error if you then try to save a querydef named tblPerson.

In the RecordSource property for the form, simply enter (or choose from the pick list) the name of the pass-through query. No code is required. Just make sure the Returns Records property is set to true.

 

[Scott_Hall]

@
Scott_Hall,
I agree doing this on the server side will make the most sense. However out of curiosity, since you are creating the disconnected recordset and then pulling data to populate it, could you have just made a temp table and populated that instead? What was the advantage of the disconnected recordset over having a predefined table?

I'm considering a temp table as my next step. I've always just been personally averse to them because of bloat (even when it is not truly an issue). Just personal preference, but I may be going that direction as it appears to be the best option.

 

[Scott_Hall]

It is NOT an adodb virtual recordset since obviously that isn't working for you.

Pass through queries are queries written in the target language of the server. Therefore, you would use T-SQL. You said your process was too complicated to do as a simple query and that is why I suggested creating a stored procedure. As long as your stored procedure returns only a single recordset, you will not have any trouble executing it with a pass through query. Since pass-through queries are the language of the server rather than Access SQL, the QBE will switch to SQL view when you tell it you want to create a pass-through query and you just type whatever you want. When you save the querydef, you can create a connection string.

In Access, querydefs and tables are interchangeable for most purposes. That is why you can't have a querydef that is the same name as a table. You can call a form tblPerson and a report as tblPerson and a table as tblPerson but you will get a duplicate error if you then try to save a querydef named tblPerson.

In the RecordSource property for the form, simply enter (or choose from the pick list) the name of the pass-through query. No code is required. Just make sure the Returns Records property is set to true.

What I mean is that I currently have one function that creates a disconnected recordset and then a second function that populates it. Then a simple

Me.Recordset=ADORST

I am not using SQL server or any other source beyond an Access back-end file.

Did you mean that I can somehow pass that resulting ADORST recordset through a query that will make it solid enough for the datasheet to see it without losing options?

 

[CJ_London]

I'm considering a temp table as my next step. I've always just been personally averse to them because of bloat

bloat can be completely avoided - use vba to create a temporary db for your temporary table - then delete the temporary db when finished

 

[MajP]

Here is an example of creating temp db as stated above
https://www.experts-exchange.com/ar...ing-Temporary-Tables-in-Microsoft-Access.html

 

[arnelgp]

You may also elect to use linked temp table from external scratch db so your orig db will not bloat.

 

[MajP]

However, since your data resides on a backend database why would you care if the front end bloats. Compact on close the front end. There is no concern there. Worst happens they get a new front end and no data lost except maybe a personal settings table.

 

[Scott_Hall]

However, since your data resides on a backend database why would you care if the front end bloats. Compact on close the front end. There is no concern there. Worst happens they get a new front end and no data lost except maybe a personal settings table.

Excellent points all around. Some gut level response made me reject the idea of temp tables, but really with a split database the objection is moot.

Thank you all for the great feedback and links

 

[Pat Hartman]

I am not using SQL server or any other source beyond an Access back-end file.

I'm sure you think this is slick. You might try a more pedestrian approach that uses a temp table if you can't figure out the SQL required to do what you need in a query. Tables that are truly temp should be created in a separate .accdb that is created on the fly so you don't bloat the FE or the BE. I have a couple of applications that load data from an ERP every day (the PTB flatly refuse to allow "Access" to link to THEIR database). I keep a template database with just the table definitions and indexes, etc but empty tables. When I need to refresh the data, the procedure copies the template to the standard BE directory and renames it to the standard name for this particular BE so my links never break. Then run the downloads into the stand alone database. You can do something similar for temp tables.