ODBC DBI Temp Tables

nathaniels has asked for the wisdom of the Perl Monks concerning the following question:

Hello All!

I'm having trouble creating a temporary table in Microsoft Access through DBI. I've tried what seems to be the standard convention:"

my $sth = $db->prepare("SELECT * INTO #temp FROM CharacterTbl") or die
+ "Couldn't prepare statement: " . $db->errstr;
    $sth->execute() or die $sth->errstr;
    
    $sth->finish;
[download]

However this gives me the error: "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

Am I missing something or is there a work around?

Thanks!

Comment on ODBC DBI Temp Tables Download Code

Replies are listed 'Best First'.
Re: ODBC DBI Temp Tables by NetWallah (Canon) on Mar 20, 2014 at 06:10 UTC
I'm guessing "temp" may be a reserved word when accessing via DBI/ODBC. Try naming your temp table differently - it looks like "tmp" is reserved as well, so try "temp123" or something. What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against? -Larry Wall, 1992	[reply]
Re: ODBC DBI Temp Tables by mje (Curate) on Mar 20, 2014 at 15:25 UTC
I'm not sure you can use temporary tables in MS Access but IF you can it may very well suffer from this problem in the DBD::ODBC FAQ.	[reply]
Re^2: ODBC DBI Temp Tables by nathaniels (Acolyte) on Mar 21, 2014 at 04:16 UTC
Thank you for that link MJE. I'll have to play around with that later and see if that helps. I may just have to give up on using a temporary table...	[reply]
Re: ODBC DBI Temp Tables by Anonymous Monk on Mar 20, 2014 at 12:21 UTC
As far as I can recall, Microsoft Access does not support temporary tables, or at least, does not expose them to SQL. I could be mistaken on this, and in saying this I am thinking specifically of the (now, old ...) JET Engine. Check this in the documentation first. (And if I am wrong, please make a note of that here.)	[reply]