Insert into problem

[Steve Kelley]

Hello all. I'm new to this group and I'm working on my first real VB.Net
project. I'm having trouble creating and archive of a database. We use
an Access database to store results from our instruments and need to
give our customers the ability to archive their results and prune the
archived data from the main database.

This query string works in .Net with the connection string defined to
point to the parent database.
"insert into ..\myArchive.Results select * from Results where " &
queryString

This does not work:
"insert into " & strPath & "\myArchive.Results select * from Results
where " & queryString

In this case strPath is the full path to the parent database and is the
same as is defined in the connection string. In both cases queryString
is the same. The error I get is "Incomplete query clause" but the only
difference is the path to the archive database. BTW, both paths point to
the same database file.

I really need to be able to use the path to the parent database to set
the path to the archive. Any ideas?

Thanks

 

[Phil]

This query string works in .Net with the connection string defined to
point to the parent database.
"insert into ..\myArchive.Results select * from Results where " &
queryString

This does not work:
"insert into " & strPath & "\myArchive.Results select * from Results where
" & queryString

In this case strPath is the full path to the parent database and is the
same as is defined in the connection string. In both cases queryString is
the same. The error I get is "Incomplete query clause" but the only
difference is the path to the archive database. BTW, both paths point to
the same database file.

Just a guess, but does the path contain spaces?
You may need to put quotes around the table name.

 

[Phil]

This query string works in .Net with the connection string defined to
point to the parent database.
"insert into ..\myArchive.Results select * from Results where " &
queryString

This does not work:
"insert into " & strPath & "\myArchive.Results select * from Results where
" & queryString

In this case strPath is the full path to the parent database and is the
same as is defined in the connection string. In both cases queryString is
the same. The error I get is "Incomplete query clause" but the only
difference is the path to the archive database. BTW, both paths point to
the same database file.

Just a guess, but does the path contain spaces?
You may need to put quotes around the table name.

 

[Cor Ligthert[MVP]]

Steve,

I have never seen (and I have seen a lot) a Select string in .Net with a
fysical drive path in it.
(That can be in a connection string).

www.connectionstrings.Com


However maybe somebody in the newsgroup.

can help you.

There are people more specialized in Adonet then here.
(Not saying about that knowledge of anybody here active)

Cor

 

[Steve Kelley]

Steve,

I have never seen (and I have seen a lot) a Select string in .Net with a
fysical drive path in it.
(That can be in a connection string).

www.connectionstrings.Com


However maybe somebody in the newsgroup.

can help you.

There are people more specialized in Adonet then here.
(Not saying about that knowledge of anybody here active)

Cor

OK, how do I insert into one database from another without pointing to
one of the databases? I am using an OleDbConnection for the source database.

I'll try the adonet group, thanks.

 

[Steve Kelley]

Just a guess, but does the path contain spaces?
You may need to put quotes around the table name.

I put quotes around the table name (forgot to include them in my post)
and get the error "Incomplete query clause". Before I added the quotes I
got a "Syntax error in INSERT INTO statement". I understand the latter
error but not the former. If I put a copy of the archive db in the local
directory and leave off the path the insert into works fine.

 

[Phil]

I put quotes around the table name (forgot to include them in my post) and
get the error "Incomplete query clause". Before I added the quotes I got a
"Syntax error in INSERT INTO statement". I understand the latter error but
not the former. If I put a copy of the archive db in the local directory
and leave off the path the insert into works fine.

try this:
"select * from Results in '" & strPath & "\myArchive.mdb' where " &
queryString

 

[Steve Kelley]

try this:
"select * from Results in '" & strPath & "\myArchive.mdb' where " &
queryString

I tried that. In that case I get an "incomplete query statement" error.
I have tried every combination of '[' '"' ''' I can think of and I get
different errors depending on the punctuation but if there is a ' ' in
the path name it fails. With the [] around the table name I get an error
saying the table could not be found but the error message contains the
path text and it is correct. In an experiment I tried this:

insert into C:\AAABBB\Archive.Results select * from Results where ...

The above works. If I change the directory name to C:\AAA BBB and try

insert into [C:\AAA BBB\Archive.Results] select * from Results where ...

I get a can't find 'C:\AAA BBB\Archive.Results' table error.

If I use
insert into 'C:\AAA BBB\Archive.Results' select * from Results where ...

I get a syntax error Incomplete query clause

There should be a way to do this. What gives?

 

[Phil]

I tried that. In that case I get an "incomplete query statement" error.

Are you sure you tried this exactly? and that you have the quotes exactly
where I have put them?

insert into C:\AAABBB\Archive.Results select * from Results where ...

The above works.

Yes it does. Although I can find no reference to this syntax in the Access
help.

If I change the directory name to C:\AAA BBB and try

insert into [C:\AAA BBB\Archive.Results] select * from Results where ...

I get a can't find 'C:\AAA BBB\Archive.Results' table error.

If I use
insert into 'C:\AAA BBB\Archive.Results' select * from Results where ...

I get a syntax error Incomplete query clause

Yes I tried all those two and got similar errors.
If you look in the Access Help for the FROM Clause, there is a different
syntax for referencing external tables, using the IN keyword.

There should be a way to do this. What gives?

Have you tried it using the IN keyword as I suggested?
This was the only way I could fine to reference an external table with a
path name that contained spaces.
I have only tried this in Access, not through OleDB.

 

[Steve Kelley]

I tried that. In that case I get an "incomplete query statement" error.

Are you sure you tried this exactly? and that you have the quotes exactly
where I have put them?

insert into C:\AAABBB\Archive.Results select * from Results where ...

The above works.

Yes it does. Although I can find no reference to this syntax in the Access
help.

If I change the directory name to C:\AAA BBB and try

insert into [C:\AAA BBB\Archive.Results] select * from Results where ...

I get a can't find 'C:\AAA BBB\Archive.Results' table error.

If I use
insert into 'C:\AAA BBB\Archive.Results' select * from Results where ...

I get a syntax error Incomplete query clause

Yes I tried all those two and got similar errors.
If you look in the Access Help for the FROM Clause, there is a different
syntax for referencing external tables, using the IN keyword.

There should be a way to do this. What gives?

Have you tried it using the IN keyword as I suggested?
This was the only way I could fine to reference an external table with a
path name that contained spaces.
I have only tried this in Access, not through OleDB.

Ah ha! This works
CommandText = "insert into Results in 'C:\AAA BBB\Archive.mdb' select *
from Results where ..."

Thank you.

 

[Phil]

Ah ha! This works
CommandText = "insert into Results in 'C:\AAA BBB\Archive.mdb' select *
from Results where ..."

Hurrah!