Re: SELECT statement to make a table not working... 1

[e7akerson]

I am trying to use SQL pass thru queries in Ms Access 2000 to query our Call Center tables (application is Clarify) which are on a 7.0 SQL Server located on the network.
Here is my SELECT statement:

Select id_number, creation_time INTO table_raw from table_case

When I run this query I get an error message saying:

CREATE table permission denied, database 'Clarify', owner 'dbo.'

I have tried variation on the table raw path trying to get the table to be written onto my hard drive not on the SQL Server but have not been successful and haven't seen any examples detailing an answer to this particular problem.

thanks

 

[TomSark]

SELECT INTO creates a table in the target database. If the table is already created, imply do something like this:

INSERT table_raw
(
id_number,
creation_time
)
SELECT id_number,
creation_time
FROM table_case

Doing the SELECT INTO requires that the select into/bulk copy option is turned on in the database (which also invalidates the transaction log), and, will create the table (or attempt to) every time you run it. INSERT/SELECT is much better if you don't have permissions to create the table, you don't want to invalidate your transaction log, and the table is already created, etc. etc...

Thanks,

Tom

 

[e7akerson]

Tom,

I tried your suggestion and got back another error message:

[Microsoft][ODBC SQL Server Driver] [SQL Server]
Invalid object name 'table_raw' (#208)

Don't know if I am posting my reply correctly or not.

thanks

 

[Guest_imported]

when you use an sql pass through query, this command is executed inside sqlserver therefore there is no way you can create the resulting table "table_raw" in your local hard drive. to do this, you must invoke an ordinary query :

Select id_number, creation_time INTO table_raw from table_case

where :

table_case - is an attached table from your sqlserver
table_raw - is the new table to be created inside your access database

if you want your table to be created inside sqlserver using a pass through query, your sql statement is correct :

Select id_number, creation_time INTO table_raw from table_case

but the id you used to logon to sqlserver must have an access right to create table in that particular database and make sure the select into/bulk copy option is turned on as what tom has suggested.

 

[e7akerson]

Tom and guest,

Please be patient with me. I'm sorry but the two SQL statements posted in the last message look exactly the same to me, i.e., I was instructed to invoke an ordinary query:

Select id_number, creation_time INTO table_raw from table_case

And this would allow me to create a table on my local hard drive not in the SQL Server.

Down in the message the same statement says that I will be able to create a table in the SQL Server if I use the exact same statement and turn on the select into/bulk copy option.

Now I am confused.

Thanks,
Eric Akerson