Error message when running SQL from code

[shaggles]

I keep getting an error message that says "Query input
must contain at least one table or query" when I try to
run the following SQL statment in VB:

DoCmd.RunSQL "INSERT INTO Tracking1 ( Name, SS, [Date of
Birth], [Residence Address], [Residence City], [Residence
State], [Residence Zip], [Residence Phone], [Business
Phone], BID, MID, Status, Position )" & _
"SELECT [Credit-New].Name, [Credit-New].NID, [Credit-
New].Birthdate, [Home Address 1] & "" "" & [Home Address
2] AS [Home Address], [Credit-New].[Home City], [Credit-
New].[Home State], [Credit-New].[Home Postal], [Credit-
New].[Home Phone], [Credit-New].[Business Phone],
[Branches with DSM].BID, ""133"" AS MID, ""P"" AS
Status, ""Credit/Life"" AS Position" & _
"FROM [Credit-New] INNER JOIN [Branches with DSM] ON
[Credit-New].Location = [Branches with DSM].MailCode" & _
"WHERE ((([Credit-New].[Job Code]) In
(""srgh0"",""srgh1"",""srdq1"",""srdq2"",""srdq3"",""crsv5"
",""crsv6"")))"

I can't figure out what's wrong with it. It runs fine as
a SQL query outside of VB. What am I doing wrong?

 

[losmac]

DoCmd - it's object of MS Access. To run this query use
this code:

Function RunMySQL() As Long
Dim dbs As Database
Dim zap As String
Const dbsPath As String = "C:\a.mdb"

zap = "INSERT INTO Table1 (Field1, Field2) VALUES
(""a"", ""b"");"

Set dbs = OpenDatabase(dbsPath)
dbs.Execute zap
RunMySQL = dbs.RecordsAffected 'how many changes was
made
dbs.Close
Set dbs = Nothing

End Function

Remember, add reference to DAO
Tools -> References -> DAO (version) Library
avalible versions: 2.5, 3.5, 3.6 ...

 

[shaggles]

I'm not sure I understand your reply. I'm running this in
an Access db. DoCmd.RunSQL has always worked.

-----Original Message-----

DoCmd - it's object of MS Access. To run this query use
this code:

Function RunMySQL() As Long
Dim dbs As Database
Dim zap As String
Const dbsPath As String = "C:\a.mdb"

zap = "INSERT INTO Table1 (Field1, Field2) VALUES
(""a"", ""b"");"

Set dbs = OpenDatabase(dbsPath)
dbs.Execute zap
RunMySQL = dbs.RecordsAffected 'how many changes was
made
dbs.Close
Set dbs = Nothing

End Function

Remember, add reference to DAO
Tools -> References -> DAO (version) Library
avalible versions: 2.5, 3.5, 3.6 ...

-----Original Message-----
I keep getting an error message that says "Query input
must contain at least one table or query" when I try to
run the following SQL statment in VB:

DoCmd.RunSQL "INSERT INTO Tracking1 ( Name, SS, [Date of
Birth], [Residence Address], [Residence City], [Residence
State], [Residence Zip], [Residence Phone], [Business
Phone], BID, MID, Status, Position )" & _
"SELECT [Credit-New].Name, [Credit-New].NID, [Credit-
New].Birthdate, [Home Address 1] & "" "" & [Home Address
2] AS [Home Address], [Credit-New].[Home City], [Credit-
New].[Home State], [Credit-New].[Home Postal], [Credit-
New].[Home Phone], [Credit-New].[Business Phone],
[Branches with DSM].BID, ""133"" AS MID, ""P"" AS
Status, ""Credit/Life"" AS Position" & _
"FROM [Credit-New] INNER JOIN [Branches with DSM] ON
[Credit-New].Location = [Branches with DSM].MailCode" & _
"WHERE ((([Credit-New].[Job Code]) In

(""srgh0"",""srgh1"",""srdq1"",""srdq2"",""srdq3"",""crsv5"

",""crsv6"")))"

I can't figure out what's wrong with it. It runs fine as
a SQL query outside of VB. What am I doing wrong?


.

.

 

[Hans-Christian Francke]

I suggest you cut your SQL statement and paste it into the Query designer.
Then run it to see if it works. Check that your [Credit-New] table and
Tracking1 table exist.

 

[shaggles]

I don't think it's a problem with the SQL statement per
se. I actually created that first in the query designer
and it worked fine. Thanks anyway.
---Original Message-----

I suggest you cut your SQL statement and paste it into the Query designer.
Then run it to see if it works. Check that your [Credit- New] table and
Tracking1 table exist.


--
Kindest regards
HC Francke
www.master-office.com

"shaggles" <[email protected]> skrev i melding

I keep getting an error message that says "Query input
must contain at least one table or query" when I try to
run the following SQL statment in VB:

DoCmd.RunSQL "INSERT INTO Tracking1 ( Name, SS, [Date of
Birth], [Residence Address], [Residence City], [Residence
State], [Residence Zip], [Residence Phone], [Business
Phone], BID, MID, Status, Position )" & _
"SELECT [Credit-New].Name, [Credit-New].NID, [Credit-
New].Birthdate, [Home Address 1] & "" "" & [Home Address
2] AS [Home Address], [Credit-New].[Home City], [Credit-
New].[Home State], [Credit-New].[Home Postal], [Credit-
New].[Home Phone], [Credit-New].[Business Phone],
[Branches with DSM].BID, ""133"" AS MID, ""P"" AS
Status, ""Credit/Life"" AS Position" & _
"FROM [Credit-New] INNER JOIN [Branches with DSM] ON
[Credit-New].Location = [Branches with DSM].MailCode" & _
"WHERE ((([Credit-New].[Job Code]) In
(""srgh0"",""srgh1"",""srdq1"",""srdq2"",""srdq3"",""crsv5"
",""crsv6"")))"

I can't figure out what's wrong with it. It runs fine as
a SQL query outside of VB. What am I doing wrong?

.

 

[losmac]

Sorry,

Now I thing, I know how to help You.

Take a look for your SQL statement. Behind words
INSERT ... SELECT ... FROM and WHERE You don't have space.
Correct this problem like this:
DoCmd.RunSQL "INSERT INTO Tracking1 ( Name, SS, [Date of
Birth], [Residence Address], [Residence City], [Residence
State], [Residence Zip], [Residence Phone], [Business
Phone], BID, MID, Status, Position )" & _
" SELECT [Credit-New].Name, [Credit-New].NID, [Credit-
New].Birthdate, [Home Address 1] & "" "" & [Home Address
2] AS [Home Address], [Credit-New].[Home City], [Credit-
New].[Home State], [Credit-New].[Home Postal], [Credit-
New].[Home Phone], [Credit-New].[Business Phone],
[Branches with DSM].BID, ""133"" AS MID, ""P"" AS
Status, ""Credit/Life"" AS Position" & _
" FROM [Credit-New] INNER JOIN [Branches with DSM] ON
[Credit-New].Location = [Branches with DSM].MailCode" & _
" WHERE ((([Credit-New].[Job Code]) In
(""srgh0"",""srgh1"",""srdq1"",""srdq2"",""srdq3"",""crsv5"
",""crsv6"")))"

For MS Access your sql statement looks like:
INSERT...SELECT...FROM...WHERE
but correct is:
INSERT ... SELECT ... FROM ... WHERE

Use MsgBox "sqlstatement" before You run your sql
statement.

losmac