how to create new table using external database

[Vinod]

Hi All,

I've a reaquirement which needs to connect to external database (SQL Server
2005) and needs to create a new table in local database (access 2003) using
retrieved results from external database.

For above requirement I've written the following code:
Set qdfPass = Nothing
Set qdfPass = CurrentDb.QueryDefs("qryPass")

'//Create connection string to qrypass
qdfPass.Connect = "ODBC;DRIVER={SQL
Server};SERVER=PERSONAL-4B2728\SQLEXPRESS;DATABASE=emp;Trusted_Connection"

'//Update qrypass sql statement
qdfPass.SQL = "Select * From sales"

CurrentDb.QueryDefs.Refresh
CurrentDb.Execute "Select * Into tblTemp_Import FROM qryPass",
dbFailOnError

Set qdfPass = Nothing

I've been getting following error while executing "Select * Into
tblTemp_Import FROM qryPass", dbFailOnError

Error number '3323'
The query can not be used as a row source.

Please help me in resolving above issue by sharing your ideas and thoughts
which will be appreciated.

Advanced Thanks
Vinod

 

[Danny J. Lesandrini]

Vinod:

I'm not sure if this is a limitation of the SELECT INTO call from within the
Access database or not. It could be that the Pass Through query obfuscates
the query's table structure and the INTO won't work. I guess I'd try something
different, like creating the table first, and then selecting rows for INSERT.

Is there a reason why that approach wouldn't work in this case? Is it that you
don't/won't know the column list?

 

[Vinod]

Thanks Danny for your response,

Yes, I've a specific reason to create a new table on fly since I'm using 20
queries (with different columns) like this to import data into access
database. Once I get data into temp table I'll use that temp table data into
required table.

Few days back I got this 'CurrentDb.Execute "Select * Into tblTemp_Import
FROM qryPass",
dbFailOnError' statement from this forum only. It was working fine at that
moment but I'm not sure why it is not working now.

Every time I've to get data in to new table only. Based on my requirement
please help meout in resolving said issue.

Thanks
Vinod

 

[Danny J. Lesandrini]

Yeah, I've got a situation where I do the same thing, but it's SQL Server
based result table so I'm not seeing the error you describe.

It's good to know that it used to work. Any idea what changed? A property
in the PT Query or maybe a field of the table, like a BLOB was added?

Off the top of my head, I can't figure why it wouldn't work. If you double-click
on qryPass does it open correctly? Can you create a Make Table query
manually with qryPass when this error occurs? It's just a matter of tracking
down the anomoly that's causing the problem.

What if you created qryPass2 which was basically SELECT * FROM qryPass
and ran the INSERT INTO from qryPass2?

 

[Vinod]

Again thank Danny for your quick response,

After assigning sql statement to 'qryPass' I'm not able to get executed
'qryPass' by double clicking on it. I've got below message after that its not
showing any thing.

‘You are about run pass-through query that may modify data in your table.
Are you sure you want to run this type of an SQL Query?’

There is no result after clicking on ‘Yes’


Here I'm repeating my code once again.
For above requirement I've written the following code:
Set qdfPass = Nothing
Set qdfPass = CurrentDb.QueryDefs("qryPass")

'//Create connection string to qrypass
qdfPass.Connect = "ODBC;DRIVER={SQL
Server};SERVER=PERSONAL-4B2728\SQLEXPRESS;DATABASE=emp;Trusted_Connection"

'//Update qrypass sql statement
qdfPass.SQL = "Select * From sales"

CurrentDb.QueryDefs.Refresh
CurrentDb.Execute "Select * Into tblTemp_Import FROM qryPass",
dbFailOnError

Set qdfPass = Nothing

Thanks
Vinod

 

[Danny J. Lesandrini]

Well, I think that messge box is the problem. I just don't recall ever getting that
message with any of my PT queries before. I wonder if it's part of the macro
security, which I immediately disable.

Alternatively, you will need to play around with the properties of the PT Query.
One of them is bound to be the problem, unless the SQL inside the query really
is an UPDATE, INSERT or DELETE query. Can you verify that the SQL is getting
changed to the inocuous SELECT * FROM tlbSales?

 

[a a r o n . k e m p f]

you could always use DDL to create objects, I mean-- that's what it is
designed for!!!

you should especially learn this method if you're using SQL Server--
DDL is how most real databases create objects

DAO is obsolete, and it has been for a decade.. anyone that still uses
it for any reason-- is obsolete and should be replaced

 

[Michael Gramelspacher]

'//Create connection string to qrypass
qdfPass.Connect = "ODBC;DRIVER={SQL
Server};SERVER=PERSONAL-4B2728\SQLEXPRESS;DATABASE=emp;Trusted_Connection"

Trusted_Connection=Yes;

Works for me.

 

[Aaron Kempf knows nothing]

I ate my shorts, I mean-- that's what it is
designed for!!!

I should be replaced