What's wrong!

[khalid]

What's wrong with this query?

Dim strSourcePath As String
   strSourcePath = "C:\01 - Service Contracts-TEST\Service Contracts Back-End.accdb"
   
   'get only logged in user allowed contracts data
   
   Dim strSQL As String
   strSQL = "INSERT INTO tblItem_Local"
   strSQL = strSQL + " SELECT tblItem.*"
   strSQL = strSQL + " FROM  [" & strSourcePath & "].tblItem"
   strSQL = strSQL + " WHERE (([" & strSourcePath & "].tblItem.ContractNo) "
   strSQL = strSQL + " In ((SELECT  UserAccess_T.[ContractNo]"
   strSQL = strSQL + " FROM UserAccess_T WHERE  [UserAccess_T].[F_KOCID]) = " & TempVars!gUserID & "));"
   CurrentDb.Execute (strSQL)

Error Msg: '' is not a valid name. Make sure that it does not include invalid charactors or punchuation that is not too long.

[jgelpi16]

You can write SQL a little bit differently when using CurrentDb.Execute. Below is a link for syntax. For one you don't need the "tblItem." portion. You can just "SELECT * FROM...". Try CurrentDb.Execute strSQL, dbFailOnError. I have never used () when using CurrentDb.Execute.

http://bytes.com/topic/access/insigh...access-queries

[khalid]

Hi jgelpi16,

The CurrentDb.Execute strSQL, dbFailOnError gives the same error.

Actually the "tblItem." is not a local table. it is in another database in:

strSourcePath = "C:\01 - Service Contracts-TEST\Service Contracts Back-End.accdb"

This is not a link table also, I want to reffer the tblItem.ContractNo field in IN(Select Statment):
WHERE (([" & strSourcePath & "].tblItem.ContractNo)

But can't figure it out where is the mess.

[June7]

Is gUserID text or number? If text need apostrophe delimiters. Otherwise, looks okay.

Step debug. Debug.Print strSQL. Did the sql compose correctly?

[khalid]

Hi, June!
gUserID is numeric value so it doesn't need the apostrophe.

[June7]

jgelpi16 has a point, probably don't need to repeat the path in the WHERE clause.

Did step debug and Debug.Print reveal anything?

[khalid]

I use the break point on the code and found some parentheses missing problems, then syntax error message, which were corrected, lastly this error message

Error Msg: '' is not a valid name. Make sure that it does not include invalid characters or punctuation that is not too long.
I am not sure if it will give reference to the desire table’s field without giving the path in WHERE clause.

[jgelpi16]

I think there may be one too many ")"? Here is what you posted...

Dim strSQL As String
   strSQL = "INSERT INTO tblItem_Local"
   strSQL = strSQL + " SELECT tblItem.*"
   strSQL = strSQL + " FROM  [" & strSourcePath & "].tblItem"
   strSQL = strSQL + " WHERE (([" & strSourcePath & "].tblItem.ContractNo) "
   strSQL = strSQL + " In ((SELECT  UserAccess_T.[ContractNo]"
   strSQL = strSQL + " FROM UserAccess_T WHERE  [UserAccess_T].[F_KOCID]) = " & TempVars!gUserID & "));"
   CurrentDb.Execute (strSQL)

Is that closed paren supposed to be there?

[jgelpi16]

Error Msg: '' is not a valid name. Make sure that it does not include invalid characters or punctuation that is not too long.
I am not sure if it will give reference to the desire table’s field without giving the path in WHERE clause.

I've received this error message when I have begun to have corruption issues. Have you ran compact & repair on both DBs?

[khalid]

Hi, jgelpi16,

Removing )
FROM UserAccess_T WHERE [UserAccess_T].[F_KOCID]) =

gives syntax error.

[khalid]

I compact and repair both the dbs, same error...

[June7]

There is an even number parens but maybe that pair is not needed.

Try the shorter WHERE clause, without the source string and table, just the field.

strSQL = strSQL & " WHERE ContractNo"
strSQL = strSQL & " In (SELECT UserAccess_T.[ContractNo]"
strSQL = strSQL & " FROM UserAccess_T WHERE [UserAccess_T].[F_KOCID] = " & TempVars!gUserID & ");"

[khalid]

Astonishing error. The first time I am facing

[khalid]

jgelpi16, there is an even number parens but maybe that pair is not needed.

Try the shorter WHERE clause, without the source string and table, just the field.

strSQL = strSQL & " WHERE ContractNo"
strSQL = strSQL & " In (SELECT UserAccess_T.[ContractNo]"
strSQL = strSQL & " FROM UserAccess_T WHERE [UserAccess_T].[F_KOCID] = " & TempVars!gUserID & ");"

Great! that's it.

Thank you very much both of you the above code did the tricks.

   Dim strSQL As String
   strSQL = "INSERT INTO tblItem_Local"
   strSQL = strSQL + " SELECT tblItem.*"
   strSQL = strSQL + " FROM  [" & strSourcePath & "].tblItem"
   strSQL = strSQL + " WHERE ContractNo"
   strSQL = strSQL + " In (SELECT UserAccess_T.[ContractNo]"
   strSQL = strSQL + " FROM UserAccess_T WHERE [UserAccess_T].[F_KOCID] = " & TempVars!gUserID & ");"
   
   CurrentDb.Execute strSQL, dbFailOnError

Actually June7 is right, there is no need for the path again in the WHERE clause.

Thanks dude!

[jgelpi16]

Glad you got it worked out!