Web Development

Ask something
Discussion Locked

VB6 Data Report Grouping

0 helpful
2 replies

by briansmart

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a vb6 application connecting to an Access 2000 database. a datareport based on an SQL that is grouped returns the following error when opened.

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

The SQL works fine in the design window so it would appear to be something to do with the grouping which is done from within the command properties Grouping section.

Please help ASAP

Thanks

Brian

2 replies

by briansmart

Further to the above, i now realize the problem is with the following code:

gstrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & gstrDataPath & gstrDataBaseName & ";User ID=" & gstrUserName & ";Password=" & gstrPassword & ";Jet OLEDB:System database=" & gstrDataPath & gstrSystemDBName

DE1.cnn1.ConnectionString=gstrConn
DE1.cnn1.Open

When the program is run like this, it works fine except fot the reports. (User level security through ADOX is perfect)

If i REM out these last two lines so that the program uses the connection defined at design time, the reports will work.

Any ideas yet?

Please........ I'm running out of time

by briansmart

I've now resolved this problem through painstaking debugging, trial and error and determination.

The problem was with the 'Provider' part of the connection string. It would appear that any attempt to change the provider at runtime causes some confliction with the SHAPE command and the syntax of the SQL for the shape. My resolution is to leave the provider alone since it works in design time and although it is mentioned in the ConnectionString, it is the same provider as that used at both run and design time. The parts of the connection string that are variable are 'User ID', 'Password', 'Data Source' and 'System Database' so change these using the properties method of the connection object.

DE1.cnn1.Properties("User ID")=gstrUserName
DE1.cnn1.Properties("Password")=gstrPassword
DE1.cnn1.Properties("Data Source")=gstrDataPath & gstrDatbaseName
DE1.cnn1.Properties("Extended Properties")="Jet OLEDB:System Database=" & gstrDataPath & gstrSystemDBName

I would still be interested to here any comments anyone has

This discussion is closed to new replies.