Insert into problem

  • Thread starter Thread starter Steve Kelley
  • Start date Start date
S

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
 
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.
Click to expand...

Just a guess, but does the path contain spaces?
You may need to put quotes around the table name.
 
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.
Click to expand...

Just a guess, but does the path contain spaces?
You may need to put quotes around the table name.
 
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
 
Cor said:
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
Click to expand...

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.
 
Phil said:
Just a guess, but does the path contain spaces?
You may need to put quotes around the table name.
Click to expand...
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.
 
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.
Click to expand...

try this:
"select * from Results in '" & strPath & "\myArchive.mdb' where " &
queryString
 
Phil said:
try this:
"select * from Results in '" & strPath & "\myArchive.mdb' where " &
queryString
Click to expand...
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?
 
I tried that. In that case I get an "incomplete query statement" error.
Click to expand...

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.
Click to expand...

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
Click to expand...

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?
Click to expand...

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.
 
Phil said:
I tried that. In that case I get an "incomplete query statement" error.
Click to expand...

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.
Click to expand...

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
Click to expand...

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?
Click to expand...

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.
Click to expand...

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

Thank you.
 
Back
Top