SQL syntax over a network

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone tell me the correct syntax for an INSERT INTO statement which will
work from a FE on any networked machine to the BE on the server. I have the
network address for the BE in a variable called strPath but I get syntax
errors when I try to run the code, which is as follows.

DoCmd.RunSQL "INSERT INTO TableName (FieldList)" _
& " IN " 'MS Access;Database=" & strPath & "'" _
& " SELECT FieldList" _
& " FROM OtherTableName;"

Strangely, I have another piece of code which contains the identical IN
statement and works perfectly, this is

DoCmd.RunSQL "SELECT FieldList" _
& " INTO TableName" _
& " IN '' 'MS Access;Database=" & strPath & "'" _
& " FROM [Pupil details]" _
& " WHERE Field = 'XXXX'" _
& " ORDER BY Field1,Field2];"

Could anyone shed any light on this?

Many thanks, Wesley Kendrick
 
Wez.k said:
Can anyone tell me the correct syntax for an INSERT INTO statement
which will work from a FE on any networked machine to the BE on the
server. I have the network address for the BE in a variable called
strPath but I get syntax errors when I try to run the code, which is
as follows.

DoCmd.RunSQL "INSERT INTO TableName (FieldList)" _
& " IN " 'MS Access;Database=" & strPath & "'" _
& " SELECT FieldList" _
& " FROM OtherTableName;"

Strangely, I have another piece of code which contains the identical
IN statement and works perfectly, this is

DoCmd.RunSQL "SELECT FieldList" _
& " INTO TableName" _
& " IN '' 'MS Access;Database=" & strPath & "'" _
& " FROM [Pupil details]" _
& " WHERE Field = 'XXXX'" _
& " ORDER BY Field1,Field2];"

Could anyone shed any light on this?

Many thanks, Wesley Kendrick
Click to expand...

It looks like those are paraphrased SQL statements, not the real thing.
That makes it very difficult to debug them. I suggest you build the SQL
statement first in a string variable, then use Debug.Print to print it
to the immediate window before attempting to execute it; like this:

Dim strSQL As String

strSQL = "INSERT INTO TableName (FieldList)" _
& " IN " 'MS Access;Database=" & strPath & "'" _
& " SELECT FieldList" _
& " FROM OtherTableName;"

Debug.Print strSQL ' *** remove after debugging ***
DoCmd.RunSQL strSQL

Copy the printed SQL string and paste it into a reply along with the
exact text of the error message. You can probably use Ctrl+C to copy
the error message from the message box, and then Ctrl+V to paste it into
your reply.
 
Thanks for your reply Dirk, I am most grateful. If by paraphrased SQL you
mean copied from the SQL view of a Query, that is correct. I have followed
your instructions and the error message was

Runtime error 3131
Syntax error in FROM clause

The contents of the immediate window are as follows
INSERT INTO SYMGrpInstReqDetailsOUT ( ID, firstname, lastname, [instrument
1], [instrument 2], [instrument 3] ) IN 'C:\SYM\SYM Main Database.mdb'
SELECT [Pupil details].ID, [Pupil details].firstname, [Pupil
details].lastname, [Pupil details].[instrument 1], [Pupil
details].[instrument 2], [Pupil details].[instrument 3] FROM [Pupil details]
IN \\Wez\C on Wez\SYM\SYM Main Database_be.mdb WHERE ((([Pupil details].[SYM
group 1])=[Forms]![Select SYM group form (registers)]![Combo0]) AND (([Pupil
details].[tuition single/multiple])='2') AND (([Pupil details].[SYM group 1
current])='-1')) OR ((([Pupil details].[tuition single/multiple])='2') AND
(([Pupil details].[SYM group 2])=[Forms]![Select SYM group form
(registers)]![Combo0]) AND (([Pupil details].[SYM group 2 current])='-1')) OR
((([Pupil details].[tuition single/multiple])='2') AND (([Pupil details].[SYM
group 3])=[Forms]![Select SYM group form (registers)]![Combo0]) AND (([Pupil
details].[SYM group 3 current])='-1')) OR ((([Pupil details].[tuition single/m
ultiple])='2') AND (([Pupil details].[SYM group 4])=[Forms]![Select SYM
group form (registers)]![Combo0]) AND (([Pupil details].[SYM group 4
current])='-1')) OR ((([Pupil details].[tuition single/multiple])='2') AND
(([Pupil details].[SYM group 5])=[Forms]![Select SYM group form
(registers)]![Combo0]) AND (([Pupil details].[SYM group 5 current])='-1')) OR
((([Pupil details].[tuition single/multiple])='2') AND (([Pupil details].[SYM
group 6])=[Forms]![Select SYM group form (registers)]![Combo0]) AND (([Pupil
details].[SYM group 6 current])='-1')) ORDER BY [Pupil details].lastname,
[Pupil details].[instrument 1];

Regards, Wesley
 
Wez.k said:
Thanks for your reply Dirk, I am most grateful. If by paraphrased
SQL you mean copied from the SQL view of a Query, that is correct. I
have followed your instructions and the error message was

Runtime error 3131
Syntax error in FROM clause

The contents of the immediate window are as follows
INSERT INTO SYMGrpInstReqDetailsOUT ( ID, firstname, lastname,
[instrument 1], [instrument 2], [instrument 3] ) IN 'C:\SYM\SYM Main
Database.mdb' SELECT [Pupil details].ID, [Pupil details].firstname,
[Pupil details].lastname, [Pupil details].[instrument 1], [Pupil
details].[instrument 2], [Pupil details].[instrument 3] FROM [Pupil
details] IN \\Wez\C on Wez\SYM\SYM Main Database_be.mdb WHERE
((([Pupil details].[SYM group 1])=[Forms]![Select SYM group form
(registers)]![Combo0]) AND (([Pupil details].[tuition
single/multiple])='2') AND (([Pupil details].[SYM group 1
current])='-1')) OR ((([Pupil details].[tuition
single/multiple])='2') AND (([Pupil details].[SYM group
2])=[Forms]![Select SYM group form (registers)]![Combo0]) AND
(([Pupil details].[SYM group 2 current])='-1')) OR ((([Pupil
details].[tuition single/multiple])='2') AND (([Pupil details].[SYM
group 3])=[Forms]![Select SYM group form (registers)]![Combo0]) AND
(([Pupil details].[SYM group 3 current])='-1')) OR ((([Pupil
details].[tuition single/m ultiple])='2') AND (([Pupil details].[SYM
group 4])=[Forms]![Select SYM
group form (registers)]![Combo0]) AND (([Pupil details].[SYM group 4
current])='-1')) OR ((([Pupil details].[tuition
single/multiple])='2') AND (([Pupil details].[SYM group
5])=[Forms]![Select SYM group form (registers)]![Combo0]) AND
(([Pupil details].[SYM group 5 current])='-1')) OR ((([Pupil
details].[tuition single/multiple])='2') AND (([Pupil details].[SYM
group 6])=[Forms]![Select SYM group form (registers)]![Combo0]) AND
(([Pupil details].[SYM group 6 current])='-1')) ORDER BY [Pupil
details].lastname, [Pupil details].[instrument 1];

Regards, Wesley
Click to expand...

Huh? But that doesn't match the syntax that you originally said it did;
viz.:
DoCmd.RunSQL "SELECT FieldList" _
& " INTO TableName" _
& " IN '' 'MS Access;Database=" & strPath & "'" _
& " FROM [Pupil details]" _
& " WHERE Field = 'XXXX'" _
& " ORDER BY Field1,Field2];"
Click to expand...

Not only does your target table's IN clause not match that syntax, but
you are trying to use yet another IN clause in the specification of your
source table:
FROM [Pupil
details] IN \\Wez\C on Wez\SYM\SYM Main Database_be.mdb
Click to expand...

Not only does this one fail to follow that syntax, it even leaves off
the quotes. And that isn't the biggest problem -- the most serious
problem is that you can't have IN clauses referring to two separate
external databases in the same statement. See the help file topic for
"IN Clause" in the Jet SQL Reference.
 
problem is that you can't have IN clauses referring to two separate
external databases in the same statement. See the help file topic
Click to expand...

You can, in this kind of example. You can code in one database,
use another for the source, and use a third for the destination.
If anything, the restriction mentioned in the help files refers
to using multiple IN clauses for the data source.

Haven't tried it with an ODBC data source or destination: but
would not be surprised if it failed if it got that far: (will
make table queries work against an ODBC data source/target?)
Presumably (don't know, haven't tested) Access will reject SQL
where there are multiple source /or/ destination IN clauses.
Access /will/ allow queries/linked tables as sources, and works
correctly with multiple disparate source MDB's when done that
way. Access can't correctly join ODBC linked tables from disparate
sources, it fails with unpredictable results, so you would not
be surprised if you got a similar failure when writing from one
ODBC source connection to a different ODBC target connection.


FWIW, Jet will accept a number of formats for this kind of
'action query', but this one is new to me! I've never seen
'MsAccess' shown explicitly, and I wouldn't have expected
it to work in Jet SQL ... which leads me to wonder if the
'working' example is for Jet SQL or ADO 'ANSI' SQL:
IN '' 'MSAccess;Database=/dbFullPath/'
In Jet SQL 'MSAccess' is assumed before the semicolon, (linked
table connect strings look like this: ';Database=...') and the
database identifier is what goes in the first string literal
(the '' in the original example), so you can just fill in the
first string and drop the second string:
IN '/dbFullPath/'

(
But this works just as well, and I like it even better:
select ... into [dbFullPath_1].[table1] _
from [dbFullPath_2].[table2]
)

Jet SQL is what you get by default in most? versions of Access
when you use CodeDB,CurrentDB or CurrentProject.Connection, so
if that is a valid ADO database string, you may need to check
that you are not running in Jet SQL mode.
(david)

Dirk Goldgar said:
Wez.k said:
Thanks for your reply Dirk, I am most grateful. If by paraphrased
SQL you mean copied from the SQL view of a Query, that is correct. I
have followed your instructions and the error message was

Runtime error 3131
Syntax error in FROM clause

The contents of the immediate window are as follows
INSERT INTO SYMGrpInstReqDetailsOUT ( ID, firstname, lastname,
[instrument 1], [instrument 2], [instrument 3] ) IN 'C:\SYM\SYM Main
Database.mdb' SELECT [Pupil details].ID, [Pupil details].firstname,
[Pupil details].lastname, [Pupil details].[instrument 1], [Pupil
details].[instrument 2], [Pupil details].[instrument 3] FROM [Pupil
details] IN \\Wez\C on Wez\SYM\SYM Main Database_be.mdb WHERE
((([Pupil details].[SYM group 1])=[Forms]![Select SYM group form
(registers)]![Combo0]) AND (([Pupil details].[tuition
single/multiple])='2') AND (([Pupil details].[SYM group 1
current])='-1')) OR ((([Pupil details].[tuition
single/multiple])='2') AND (([Pupil details].[SYM group
2])=[Forms]![Select SYM group form (registers)]![Combo0]) AND
(([Pupil details].[SYM group 2 current])='-1')) OR ((([Pupil
details].[tuition single/multiple])='2') AND (([Pupil details].[SYM
group 3])=[Forms]![Select SYM group form (registers)]![Combo0]) AND
(([Pupil details].[SYM group 3 current])='-1')) OR ((([Pupil
details].[tuition single/m ultiple])='2') AND (([Pupil details].[SYM
group 4])=[Forms]![Select SYM
group form (registers)]![Combo0]) AND (([Pupil details].[SYM group 4
current])='-1')) OR ((([Pupil details].[tuition
single/multiple])='2') AND (([Pupil details].[SYM group
5])=[Forms]![Select SYM group form (registers)]![Combo0]) AND
(([Pupil details].[SYM group 5 current])='-1')) OR ((([Pupil
details].[tuition single/multiple])='2') AND (([Pupil details].[SYM
group 6])=[Forms]![Select SYM group form (registers)]![Combo0]) AND
(([Pupil details].[SYM group 6 current])='-1')) ORDER BY [Pupil
details].lastname, [Pupil details].[instrument 1];

Regards, Wesley
Click to expand...

Huh? But that doesn't match the syntax that you originally said it did;
viz.:
DoCmd.RunSQL "SELECT FieldList" _
& " INTO TableName" _
& " IN '' 'MS Access;Database=" & strPath & "'" _
& " FROM [Pupil details]" _
& " WHERE Field = 'XXXX'" _
& " ORDER BY Field1,Field2];"
Click to expand...

Not only does your target table's IN clause not match that syntax, but
you are trying to use yet another IN clause in the specification of your
source table:
FROM [Pupil
details] IN \\Wez\C on Wez\SYM\SYM Main Database_be.mdb
Click to expand...

Not only does this one fail to follow that syntax, it even leaves off
the quotes. And that isn't the biggest problem -- the most serious
problem is that you can't have IN clauses referring to two separate
external databases in the same statement. See the help file topic for
"IN Clause" in the Jet SQL Reference.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
david epsom dot com dot au said:
problem is that you can't have IN clauses referring to two separate
external databases in the same statement. See the help file topic
Click to expand...

You can, in this kind of example. You can code in one database,
use another for the source, and use a third for the destination.
If anything, the restriction mentioned in the help files refers
to using multiple IN clauses for the data source.

Haven't tried it with an ODBC data source or destination: but
would not be surprised if it failed if it got that far: (will
make table queries work against an ODBC data source/target?)
Presumably (don't know, haven't tested) Access will reject SQL
where there are multiple source /or/ destination IN clauses.
Access /will/ allow queries/linked tables as sources, and works
correctly with multiple disparate source MDB's when done that
way. Access can't correctly join ODBC linked tables from disparate
sources, it fails with unpredictable results, so you would not
be surprised if you got a similar failure when writing from one
ODBC source connection to a different ODBC target connection.


FWIW, Jet will accept a number of formats for this kind of
'action query', but this one is new to me! I've never seen
'MsAccess' shown explicitly, and I wouldn't have expected
it to work in Jet SQL ... which leads me to wonder if the
'working' example is for Jet SQL or ADO 'ANSI' SQL:
IN '' 'MSAccess;Database=/dbFullPath/'
In Jet SQL 'MSAccess' is assumed before the semicolon, (linked
table connect strings look like this: ';Database=...') and the
database identifier is what goes in the first string literal
(the '' in the original example), so you can just fill in the
first string and drop the second string:
IN '/dbFullPath/'

(
But this works just as well, and I like it even better:
select ... into [dbFullPath_1].[table1] _
from [dbFullPath_2].[table2]
)

Jet SQL is what you get by default in most? versions of Access
when you use CodeDB,CurrentDB or CurrentProject.Connection, so
if that is a valid ADO database string, you may need to check
that you are not running in Jet SQL mode.
(david)
Click to expand...

Huh. I had to check this, because it was contrary to what I have long
believed, but you are right. You *can* use IN clauses for both target
and source, and you can use just the database path, without specifying
the type. I was looking at an example where it was necessary to specify
the database password, and for that I think you may need to specify it
using the whole connect string. However, I haven't tried any
alteratives recently enough to be sure of that.

That last alternate syntax -- [dbFullPath].[table1] -- is completely new
to me, but it works. Thanks for setting me straight and telling me a
nifty new thing!
 
:~) When using password/IISAM/ODBC:

[connect string].[table1]

(david)
 
:~) When using password/IISAM/ODBC:

[connect string].[table1]

(david)

Dirk Goldgar said:
david epsom dot com dot au said:
problem is that you can't have IN clauses referring to two separate
external databases in the same statement. See the help file topic
Click to expand...

You can, in this kind of example. You can code in one database,
use another for the source, and use a third for the destination.
If anything, the restriction mentioned in the help files refers
to using multiple IN clauses for the data source.

Haven't tried it with an ODBC data source or destination: but
would not be surprised if it failed if it got that far: (will
make table queries work against an ODBC data source/target?)
Presumably (don't know, haven't tested) Access will reject SQL
where there are multiple source /or/ destination IN clauses.
Access /will/ allow queries/linked tables as sources, and works
correctly with multiple disparate source MDB's when done that
way. Access can't correctly join ODBC linked tables from disparate
sources, it fails with unpredictable results, so you would not
be surprised if you got a similar failure when writing from one
ODBC source connection to a different ODBC target connection.


FWIW, Jet will accept a number of formats for this kind of
'action query', but this one is new to me! I've never seen
'MsAccess' shown explicitly, and I wouldn't have expected
it to work in Jet SQL ... which leads me to wonder if the
'working' example is for Jet SQL or ADO 'ANSI' SQL:
IN '' 'MSAccess;Database=/dbFullPath/'
In Jet SQL 'MSAccess' is assumed before the semicolon, (linked
table connect strings look like this: ';Database=...') and the
database identifier is what goes in the first string literal
(the '' in the original example), so you can just fill in the
first string and drop the second string:
IN '/dbFullPath/'

(
But this works just as well, and I like it even better:
select ... into [dbFullPath_1].[table1] _
from [dbFullPath_2].[table2]
)

Jet SQL is what you get by default in most? versions of Access
when you use CodeDB,CurrentDB or CurrentProject.Connection, so
if that is a valid ADO database string, you may need to check
that you are not running in Jet SQL mode.
(david)
Click to expand...

Huh. I had to check this, because it was contrary to what I have long
believed, but you are right. You *can* use IN clauses for both target
and source, and you can use just the database path, without specifying
the type. I was looking at an example where it was necessary to specify
the database password, and for that I think you may need to specify it
using the whole connect string. However, I haven't tried any
alteratives recently enough to be sure of that.

That last alternate syntax -- [dbFullPath].[table1] -- is completely new
to me, but it works. Thanks for setting me straight and telling me a
nifty new thing!

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top