Compile stops at db.OpenRecordset (1 Viewer)

jrickards · Jun 19, 2019

HI:

Not much more than a beginner here, just trying to use a downloaded VBscript but it is throwing an error.

Code is from: tek-tips.com/faqs.cfm?fid=4233

Using Access 365, the yellow debug line is stopped at

Set rs = db.OpenRecordset(pstrSQL)

How do I get past this?

Thanks

NauticalGent · Jun 19, 2019

Looking at the URL you provided, pstrSQL is an argument that is passed to the Function you copied. Given that you have said you are a beginner and since you did not provide the SQL string, or the error you are getting I’m can only assume you are passing a bad string.

Can you do a Debug.print on pstrSQL and provide the error info?

theDBguy · Jun 19, 2019

Or maybe post a screenshot of the code and error message? Thanks.

jrickards · Jun 19, 2019

What this function is intended to do is list the authors for each publication. For the sake of testing, I've limited the query to just one publication with multiple authors. PublicationID 00001620 has three authors and I'm wanting to get a list of them in a single field.

OK, the SQL is:

SELECT PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID, CONCATENATE("SELECT PUB_PERSON.PERSON_LASTNAME WHERE PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID=" & [PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID],",",", and ") AS Authors
FROM (PUB_PUBLICATION INNER JOIN PUB_PUBLICATION_CONTRIBUTOR ON PUB_PUBLICATION.PUBLICATION_ID = PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID) INNER JOIN (PUB_CONTRIBUTOR INNER JOIN PUB_PERSON ON PUB_CONTRIBUTOR.PERSON_ID = PUB_PERSON.PERSON_ID) ON PUB_PUBLICATION_CONTRIBUTOR.CONTRIBUTOR_ID = PUB_CONTRIBUTOR.CONTRIBUTOR_ID
WHERE (((PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID)="00001620"));

Sorry, how do I do a debug.print?

jrickards · Jun 19, 2019

Run-time error 3075

Syntax error (missing operator) in query expression 'PUB_PERSON.PERSON_LASTNAME WHERE PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID=00001620'

jrickards · Jun 19, 2019

OK, I've determined one thing. As the code in the URL above indicates in the comments, if the ID (PUBLICATION_ID) is string and not numeric, apply the changes indicated. I applied the suggested changes, no difference.

I then decided to change the PUBLICATION_ID from text to numeric (not an ideal thing to do but this is my personal copy of an Access database so I can always bring back the original from the shared drive). Now I'm getting a different error "Type mismatch in expression" which I assumed means that numeric and text are not the same but I had made the appropriate changes in both the VB code and the SQL yet I get this.

theDBguy · Jun 19, 2019

jrickards said:
OK, I've determined one thing. As the code in the URL above indicates in the comments, if the ID (PUBLICATION_ID) is string and not numeric, apply the changes indicated. I applied the suggested changes, no difference.

I then decided to change the PUBLICATION_ID from text to numeric (not an ideal thing to do but this is my personal copy of an Access database so I can always bring back the original from the shared drive). Now I'm getting a different error "Type mismatch in expression" which I assumed means that numeric and text are not the same but I had made the appropriate changes in both the VB code and the SQL yet I get this.

Hi. I'm still hoping to see a screenshot of the error message and the highlighted line when you go to Debug mode.

Cronk · Jun 19, 2019

Code:

SELECT PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID, CONCATENATE("SELECT  PUB_PERSON.PERSON_LASTNAME WHERE  PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID=" &  [B][COLOR=Red]'[/COLOR][/B][PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID][B][COLOR=red]'[/COLOR][/B],",",", and ") AS Authors
FROM (PUB_PUBLICATION INNER JOIN PUB_PUBLICATION_CONTRIBUTOR ON  PUB_PUBLICATION.PUBLICATION_ID =  PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID) INNER JOIN (PUB_CONTRIBUTOR  INNER JOIN PUB_PERSON ON PUB_CONTRIBUTOR.PERSON_ID =  PUB_PERSON.PERSON_ID) ON PUB_PUBLICATION_CONTRIBUTOR.CONTRIBUTOR_ID =  PUB_CONTRIBUTOR.CONTRIBUTOR_ID
 WHERE (((PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID)="00  001620"));

PUBLICATION_ID is a string. There are quotes around it in one WHERE clause, but on the first one (see red in code above).

BTW now I remember why I dislike all caps for table and field names.

The_Doc_Man · Jun 19, 2019

First, that function CONCATENATE is problematical.

Code:

... CONCATENATE("SELECT PUB_PERSON.PERSON_LASTNAME WHERE PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID=" & [PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID],",",", and ")...

CONCATENATE isn't a VBA function commonly used in Access databases. In fact, even with my Excel libraries loaded to my references list, I can't find it. However, it appears in online articles, so I know it exists. Note that since 2016, this function may have been renamed (shortened) to CONCAT. Access 365 is, I believe, the same "vintage" as Ac2016 so it may also know this function by the shorter name. The longer name isn't visible to my Object Browser even with the Excel library loaded. So it must be in some oddball library or else I have an issue of a different kind.

Second, the quotation marks in that string cannot possibly be right due to the way VBA handles quote marks. At some point, that string is terminating because of an isolated quote. If you look at the end of that printout in post #5, you see this:

Code:

'PUB_PERSON.PERSON_LASTNAME WHERE PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID=[COLOR="Red"]0000162 0[/COLOR]'

If you look at the end of that sequence, I highlighted in red where you have number, a space, and a number with nothing else between them. Syntax rules for SQL say you have to have an operator between two numbers in that context, and you don't. Therefore, I think that was the source of your error 3075. It occurs because you have the quotes wrong. Since I don't understand what you are doing, I can't tell you how to make the quotes right.

jrickards · Jun 20, 2019

thedbguy said:
hi. I'm still hoping to see a screenshot of the error message and the highlighted line when you go to debug mode.

jrickards · Jun 20, 2019

The_Doc_Man said:
First, that function CONCATENATE is problematical.

Code:

... CONCATENATE("SELECT PUB_PERSON.PERSON_LASTNAME WHERE PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID=" & [PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID],",",", and ")...

CONCATENATE isn't a VBA function commonly used in Access databases. In fact, even with my Excel libraries loaded to my references list, I can't find it. However, it appears in online articles, so I know it exists. Note that since 2016, this function may have been renamed (shortened) to CONCAT. Access 365 is, I believe, the same "vintage" as Ac2016 so it may also know this function by the shorter name. The longer name isn't visible to my Object Browser even with the Excel library loaded. So it must be in some oddball library or else I have an issue of a different kind.

If you have a look at the opening post, you'll see that I downloaded the code (url given but not in url format as I didn't have enough posts here to format it properly as a url).

The solution I'm looking for is to take a publication with multiple authors and convert it to a comma separated list as in:

Pub01 Author1
Pub01 Author2
Pub01 Author3

to

Pub01 Author1, Author2, Author3

(the VBA code also has the option of putting "and" before the last item in the list such as

Pub01 Author1, Author2 and Author3)

In other databases, MySQL for example, there is SQL that will do this without resorting to VBA or other scripting option, Access, it appears, can only do this with a scripting solution (unless something has changed and I haven't found the solution online).

The_Doc_Man said:
Second, the quotation marks in that string cannot possibly be right due to the way VBA handles quote marks. At some point, that string is terminating because of an isolated quote. If you look at the end of that printout in post #5, you see this:

Code:

'PUB_PERSON.PERSON_LASTNAME WHERE PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID=[COLOR="Red"]0000162 0[/COLOR]'

If you look at the end of that sequence, I highlighted in red where you have number, a space, and a number with nothing else between them. Syntax rules for SQL say you have to have an operator between two numbers in that context, and you don't. Therefore, I think that was the source of your error 3075. It occurs because you have the quotes wrong. Since I don't understand what you are doing, I can't tell you how to make the quotes right.

The extra space appears in this post only, not in the code, I don't know why it appears here, I copy/pasted the SQL text from Access to here and strangely, the space appeared (but I hadn't noticed it earlier). However, I will check the SQL code once again to make sure that there isn't a hidden space or other character.

jrickards · Jun 20, 2019

jrickards said:
The extra space appears in this post only, not in the code, I don't know why it appears here, I copy/pasted the SQL text from Access to here and strangely, the space appeared (but I hadn't noticed it earlier). However, I will check the SQL code once again to make sure that there isn't a hidden space or other character.

No space in the code, must have been a copy/paste inaccuracy.

theDBguy · Jun 20, 2019

jrickards said:
View attachment 75304

View attachment 75305

View attachment 75306

Hi. Thanks for posting those images. So, if the problem is SQL syntax, then I guess we'll just have to fix it. For example, the error message include the following:

Code:

...PUBLICATION_ID=00001620

So, the way I read that, if you have leading zeroes, then Publication_ID must be a Text field. If so, then we'll just need to make sure it comes out like this:

Code:

...PUBLICATION_ID='00001620'

Basically, we just need to enclose the value with single quotes, instead of double quotes. Please try that and let us know what happens.

jrickards · Jun 20, 2019

theDBguy said:
Hi. Thanks for posting those images. So, if the problem is SQL syntax, then I guess we'll just have to fix it. For example, the error message include the following:

Code:

...PUBLICATION_ID=00001620

So, the way I read that, if you have leading zeroes, then Publication_ID must be a Text field. If so, then we'll just need to make sure it comes out like this:

Code:

...PUBLICATION_ID='00001620'

Basically, we just need to enclose the value with single quotes, instead of double quotes. Please try that and let us know what happens.

No difference, the Debug stops at the same place "Set rs = db.OpenRecordset (pstrSQL)"

theDBguy · Jun 20, 2019

jrickards said:
No difference, the Debug stops at the same place "Set rs = db.OpenRecordset (pstrSQL)"

Would you mind posting the result of Debug.Print pstrSQL again? Thanks.

jrickards · Jun 20, 2019

How do I print debug.print pstrSQL?

theDBguy · Jun 20, 2019

jrickards said:
How do I print debug.print pstrSQL?

Hi. When you get the error, click on the Debug button on the message window. Then, in the Immediate Window, type the following:

Code:

?pstrSQL

Copy and paste the result here.

jrickards · Jun 20, 2019

Nope, nothing, compile error at the "Set rs = " line

Maybe something is wrong with how I'm using the downloaded script. Maybe I should ask where it goes so that it can be accessed by Access. I have some subroutines that I created years ago and I put it with them, maybe that's the problem?

theDBguy · Jun 20, 2019

jrickards said:
Nope, nothing, compile error at the "Set rs = " line

Maybe something is wrong with how I'm using the downloaded script. Maybe I should ask where it goes so that it can be accessed by Access. I have some subroutines that I created years ago and I put it with them, maybe that's the problem?

Hi. I wasn't going to mention it, but I also have a similar function for doing the same thing on my website. You could try and see if you'll have a better luck with it.

jrickards · Jun 20, 2019

Hah! That looks much more concise!

Let me try it in place of what I've got.

Compile stops at db.OpenRecordset (1 Viewer)

Registered User.

Ignore List Poster Boy

I’m here to help

Registered User.

Registered User.

Registered User.

I’m here to help

Registered User.

Immoderate Moderator

Registered User.

Registered User.

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Similar threads

Users who are viewing this thread