FROM and IN problem with sql 
Author Message
 FROM and IN problem with sql

I am trying to copy a table from one database into another database. I pass the
names of the two databases to a function as strings and copy and empty database
as the new database. This part works OK

Then I try to use an SQL statement to copy all the records from the table in the
 old mdb to the new one. Both the tables have the same name.

The sql statement evaluates to

    INSERT INTO depth SELECT * FROM depth IN G:\WELLPLAN\SAVE.MDB

and I get an error message "Syntax error in FROM statement". The file and the
table both exist.

Any help would be appreciated.

The full source code follows.

Kym Wilson

Function update2_2 (olddbname, newdbname) As Integer
Dim  dbnew As database

  ' copy empty database with new structure to new file

  FileCopy "empty2-2.mdb", newdbname

  ' open new table and insert records from old one

  Set dbnew = OpenDatabase(newdbname)
  sql = "INSERT INTO depth SELECT * FROM depth IN " + olddbname
  dbnew.Execute sql
  dbnew.Close
  update2_2 = True

End Function



Fri, 02 Jan 1998 03:00:00 GMT  
 FROM and IN problem with sql


Quote:
> I am trying to copy a table from one database into another

database. I pass the

Quote:
> names of the two databases to a function as strings and copy and
empty database
> as the new database. This part works OK
> Then I try to use an SQL statement to copy all the records from the
table in the
>  old mdb to the new one. Both the tables have the same name.
> The sql statement evaluates to
>     INSERT INTO depth SELECT * FROM depth IN G:\WELLPLAN\SAVE.MDB
> and I get an error message "Syntax error in FROM statement". The file and the
> table both exist.
> Any help would be appreciated.
> The full source code follows.
> Kym Wilson
> Function update2_2 (olddbname, newdbname) As Integer
> Dim  dbnew As database
>   ' copy empty database with new structure to new file
>   FileCopy "empty2-2.mdb", newdbname
>   ' open new table and insert records from old one
>   Set dbnew = OpenDatabase(newdbname)
>   sql = "INSERT INTO depth SELECT * FROM depth IN " + olddbname
>   dbnew.Execute sql
>   dbnew.Close
>   update2_2 = True

I had this problem.   As far as I understand it the IN clause refers
to the whole sql statement so it is effectively trying to insert into
itself.   I got round it by attach the table in the other database
and missing out the IN clause.

Hopefully somebody can tell us that there is a way of working with
tables in different databases without attaching because it wold be
really useful.

--

MES, 26 North Road,                  
Lerwick, Shetland, UK     Compuserve: 100016,1727
Phone: +44 1595 695762           Fax: +44 1595 694459



Fri, 02 Jan 1998 03:00:00 GMT  
 FROM and IN problem with sql


Quote:

>I am trying to copy a table from one database into another database. I pass the
>names of the two databases to a function as strings and copy and empty database
>as the new database. This part works OK

>Then I try to use an SQL statement to copy all the records from the table in the
> old mdb to the new one. Both the tables have the same name.

>The sql statement evaluates to

>    INSERT INTO depth SELECT * FROM depth IN G:\WELLPLAN\SAVE.MDB

>and I get an error message "Syntax error in FROM statement". The file and the
>table both exist.

Have you tried putting quotes around the IN clause? ie:

        INSERT INTO depth SELECT * FROM depth IN 'G:\WELLPLAN\SAVE.MDB'

Phil Boswell -------------------------------------------------------------+
(who finally got round to making a .sig file, and couldn't think of       |
 anything sufficiently witty to put in it :)                              |



Sun, 04 Jan 1998 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Sigle quote problem in SQL Statement in SQL

2. Sigle quote problem in SQL Statement in SQL

3. Year 2000 problem in SQL statments written in VBA

4. Dataview sort problems with SQL Server

5. HELP!!! Problem with SQL query

6. Beginner: DB Problem ADO / SQL

7. access 2000 problem with SQL

8. Date look up problem in SQL and VB5

9. Dynaset/Snapshot problem using SQL Server 6.5

10. Help: INNER JOIN problem, any SQL-Gurus ?

11. INSERT INTO problems VB4/SQL Server

12. VB 5 - ADO adOpenKeySet cursor problem with SQL Server stored procedures using Variables

 

 
Powered by phpBB® Forum Software