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.Re sults select * from Results where " &
queryString

This does not work:
"insert into " & strPath & "\myArchive.Res ults 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

--
Steve Kelley

[Phil]

Re: Insert into problem

>

This query string works in .Net with the connection string defined to
point to the parent database.
"insert into ..\myArchive.Re sults select * from Results where " &
queryString
>
This does not work:
"insert into " & strPath & "\myArchive.Res ults 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]

Re: Insert into problem

>

This query string works in .Net with the connection string defined to
point to the parent database.
"insert into ..\myArchive.Re sults select * from Results where " &
queryString
>
This does not work:
"insert into " & strPath & "\myArchive.Res ults 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]]

Re: Insert into problem

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).

https://www.connectionstrings.Com

However maybe somebody in the newsgroup.

news:\\microsof t.public.framew ork.adonet can help you.

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

Cor

[Steve Kelley]

Re: Insert into problem

Cor Ligthert[MVP] wrote:

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).
>

https://www.connectionstrings.Com

>
>
However maybe somebody in the newsgroup.
>
news:\\microsof t.public.framew ork.adonet 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

[Steve Kelley]

Re: Insert into problem

Phil wrote:

>This query string works in .Net with the connection string defined to
>point to the parent database.
>"insert into ..\myArchive.Re sults select * from Results where " &
>queryString
>>
>This does not work:
>"insert into " & strPath & "\myArchive.Res ults 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.
>
>

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.

--
Steve Kelley

[Phil]

Re: Insert into problem

>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.

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

[Steve Kelley]

Re: Insert into problem

Phil wrote:

>>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.

>
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\Archi ve.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.Res ults] select * from Results where ...

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

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

I get a syntax error Incomplete query clause

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

--
Steve Kelley

[Phil]

Re: Insert into problem

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

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\Archi ve.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.Res ults] select * from Results where ...
>
I get a can't find 'C:\AAA BBB\Archive.Res ults' table error.
>
If I use
insert into 'C:\AAA BBB\Archive.Res ults' 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]

Re: Insert into problem

Phil wrote:

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

>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\Archi ve.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.Res ults] select * from Results where ...
>>
>I get a can't find 'C:\AAA BBB\Archive.Res ults' table error.
>>
>If I use
>insert into 'C:\AAA BBB\Archive.Res ults' 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.

--
Steve Kelley

[Phil]

Re: Insert into problem

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

Hurrah!
:-)