Compile stops at db.OpenRecordset

[theDBguy]

Hah! That looks much more concise!

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

Thanks. Let me know if you run into any issues.

 

[jrickards]

So, to start off with, where do I put this so that it is available to the SQL? I'm wondering if that might be part of the problem.

 

[theDBguy]

So, to start off with, where do I put this so that it is available to the SQL? I'm wondering if that might be part of the problem.

Copy the code from the website. Open the VBE window and create a new Standard Module. Paste the code and save the module using either the default Module1 name or use something like modSimpleCSV. You should be able to use it in a query after that.

 

[jrickards]

OK, I had that part right. End of my day here, will continue tomorrow (same time zone as NYC).

 

[theDBguy]

OK, I had that part right. End of my day here, will continue tomorrow (same time zone as NYC).

Okay, see you tomorrow then...

 

[jrickards]

Error message: Undefined function 'SIMPLECSV' in expression.

SELECT
     PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID,
     SIMPLECSV("SELECT PUB_PERSON.PERSON_LASTNAME WHERE PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID=" & [PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID]) AS Authors

FROM
     PUB_PUBLICATION_CONTRIBUTOR 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";

 

[theDBguy]

Error message: Undefined function 'SIMPLECSV' in expression.

SELECT
     PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID,
     SIMPLECSV("SELECT PUB_PERSON.PERSON_LASTNAME WHERE PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID=" & [PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID]) AS Authors

FROM
     PUB_PUBLICATION_CONTRIBUTOR 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";

Hi. Your SQL syntax is a little off, but I'll try to address the error message first. So, just to confirm what you did, you said you copied and pasted the code from the website into a Standard Module, correct? If so, what did you name the Module itself? If you named it SimpleCSV also, then please change it to modSimpleCSV. Then, in the Immediate Window, enter the following code:

?SimpleCSV("MSysObjects")

What happened?

 

[jrickards]

Yup, module was SimpleCSV, changed it to modSimpleCSV.

Error above didn't occur again.

Not sure what you mean by Immediate Window so I couldn't do the print code.

I ran the query, back to the same errors as for the previous script CONCATENATE.

 

[jrickards]

BTW, the publication ID is text, not numeric (I have no idea why but this publications database is like the 4th or 5th generation of the original which was created in the mid- to late-80s). I could change the ID to numeric but when I need to grab the tables from Oracle again, it'll be in text again.

 

[theDBguy]

Yup, module was SimpleCSV, changed it to modSimpleCSV.

Error above didn't occur again.

Not sure what you mean by Immediate Window so I couldn't do the print code.

I ran the query, back to the same errors as for the previous script CONCATENATE.

Hi. Okay, at least we're making progress. Now, let's try it slowly. Let's start with a simple query first:

SELECT PUBLICATION_ID, SIMPLECSV("PUBLICATION_ID FROM PUB_PUBLICATION_CONTRIBUTOR") AS TEST
FROM PUB_PUBLICATION_CONTRIBUTOR 
WHERE PUBLICATION_ID="00001620"

It's not exactly what you want yet, we're just trying to see if we still get any errors.

 

[jrickards]

It is having trouble with 'PUBLICATION_ID FROM PUB_PUBLICATION_CONTRIBUTOR', MS Access database engine cannot find the table. I've checked the spelling, it's all good.

I changed that snippet to PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID but still doesn't work (the dot format is what Access generates so I tried it that way).

Run-time error '3078'

 

[theDBguy]

It is having trouble with 'PUBLICATION_ID FROM PUB_PUBLICATION_CONTRIBUTOR', MS Access database engine cannot find the table. I've checked the spelling, it's all good.

I changed that snippet to PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID but still doesn't work (the dot format is what Access generates so I tried it that way).

Run-time error '3078'

Hi. If Access is complaining it cannot find the table, then we're using the wrong name. Can you post a copy of your db with sample/dummy data? If not, can you post some screenshots? Thanks.

 

[jrickards]

Here is a screenshot

 

[theDBguy]

Here is a screenshot

View attachment 75323

Ah, sorry, my bad. Try again this way please:

SELECT PUBLICATION_ID, SIMPLECSV("SELECT PUBLICATION_ID FROM PUB_PUBLICATION_CONTRIBUTOR") AS TEST
FROM PUB_PUBLICATION_CONTRIBUTOR
WHERE PUBLICATION_ID="00001620"

 

[jrickards]

Works! What did we miss in the SQL?

 

[jrickards]

No, not quite. Test should have only 3 values but instead it has all of these.

00006258,00012197,00005824,00006024,00007887,00012200,00012201,00008563,00008594,00008595,00008603,00008768,00002307,00005369,00005449,00005450,00010580,00010581,00002860,00002881,00005440,00007670,00007691,00010567,00010632,00010633,00012069,00012077,00006806,00008173,00002222,

Does it matter that the ID field is text and not numeric?

 

[theDBguy]

Works! What did we miss in the SQL?

I missed the SELECT part.

 

[theDBguy]

No, not quite. Test should have only 3 values but instead it has all of these.

00006258,00012197,00005824,00006024,00007887,00012200,00012201,00008563,00008594,00008595,00008603,00008768,00002307,00005369,00005449,00005450,00010580,00010581,00002860,00002881,00005440,00007670,00007691,00010567,00010632,00010633,00012069,00012077,00006806,00008173,00002222,

Does it matter that the ID field is text and not numeric?

Hi. As I was trying to say earlier, we're trying to slowly build the result up just so we can catch each error message along the way. Now that we don't get any more errors, we can concentrate on getting the correct result. For example, can you post a screenshot of both table structure, so I can tell which table and which field to get the authors from? Or, you could try this guess of an attempt:

SELECT PUBLICATION_ID, SIMPLECSV("SELECT PERSON_LASTNAME FROM PUB_PERSON
WHERE PERSON_ID=" & [PERSON_ID]) AS AUTHORS
FROM PUB_PUBLICATION_CONTRIBUTOR
WHERE PUBLICATION_ID="00001620"

 

[jrickards]

OK, here's the table structure. Ultimately, I want "PERSON_LASTNAME PERSON_FIRSTNAME" (space between them and comma between each person)



I'll try that code in just a sec

 

[jrickards]

No errors in the attempt, per se, but it was asking for a PERSON_ID which, it turns out also is text (why did they create IDs that are text and not numeric? ... anyway)