• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1226
  • Last Modified:

Access VBA / SQL - Insert, Select and Where statement error

Hi Experts,

I need your help. Can someone please review this SQL VB statement for me. i cant seem to make it work from a push of a button on a form.

strSQL1c = "INSERT INTO [ar_client]([uniqueid_c],[clientcode_c],[firstname_vc],[lastname_vc],[create_dt],[createuser_c],[touch_date],[collection_c],[touch_user],[arnoteid_c])SELECT" & _
"[dbo_IDgenerator.cluniqueID],[dbo_IDgenerator.clientcode_c],[dbo_IDgenerator.firstname_vc],[dbo_IDgenerator.lastname_vc],[dbo_IDgenerator.create_dt],[dbo_IDgenerator.createuser_c],[dbo_IDgenerator.create_dt],'n' AS [collection_c],[dbo_IDgenerator.createuser_c],[dbo_IDgenerator.cluniqueID] AS arnoteid_c" & _
"From [dbo_IDgenerator] WHERE ((([dbo_IDgenerator.createstatus])='SMF-Pending'))"
0
bootyfreakk
Asked:
bootyfreakk
  • 3
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Seems you are missing an Execute statement:

strSQL1c = "INSERT INTO [ar_client]([uniqueid_c],[clientcode_c],[firstname_vc],[lastname_vc],[create_dt],[createuser_c],[touch_date],[collection_c],[touch_user],[arnoteid_c])SELECT" & _
"[dbo_IDgenerator.cluniqueID],[dbo_IDgenerator.clientcode_c],[dbo_IDgenerator.firstname_vc],[dbo_IDgenerator.lastname_vc],[dbo_IDgenerator.create_dt],[dbo_IDgenerator.createuser_c],[dbo_IDgenerator.create_dt],'n' AS [collection_c],[dbo_IDgenerator.createuser_c],[dbo_IDgenerator.cluniqueID] AS arnoteid_c" & _
"From [dbo_IDgenerator] WHERE ((([dbo_IDgenerator.createstatus])='SMF-Pending'))"

CurrentDB.Execute strSQL1c , dbFailOnError

0
 
bootyfreakkAuthor Commented:
im using
 DoCmd.RunSQL strSQL1c

 to run this statement. let me try that code.
 
0
 
Patrick MatthewsCommented:
You need more spaces:

strSQL1c = "INSERT INTO [ar_client] ([uniqueid_c],[clientcode_c],[firstname_vc],[lastname_vc], " & _
    "[create_dt],[createuser_c],[touch_date],[collection_c],[touch_user],[arnoteid_c]) " & _
    "SELECT [dbo_IDgenerator.cluniqueID],[dbo_IDgenerator.clientcode_c]," & _
    "[dbo_IDgenerator.firstname_vc],[dbo_IDgenerator.lastname_vc]," & _
    "[dbo_IDgenerator.create_dt],[dbo_IDgenerator.createuser_c],[dbo_IDgenerator.create_dt]," & _
    "'n' AS [collection_c],[dbo_IDgenerator.createuser_c],[dbo_IDgenerator.cluniqueID] AS arnoteid_c " & _
    "From [dbo_IDgenerator] WHERE [dbo_IDgenerator.createstatus]='SMF-Pending'" 

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Whereas the Execute method is better, that's not the issue then.

What Exactly is the error you are getting ?

My guess ... try this:


WHERE ((([dbo_IDgenerator.createstatus])=" & Chr(34) & "SMF-Pending" & Chr(34) ))"
0
 
bootyfreakkAuthor Commented:
still wont let me run it. i'm missing something or wrote something wrong.
 on a simple query if you paste in
INSERT INTO ar_client ( uniqueid_c, clientcode_c, firstname_vc, lastname_vc, create_dt, createuser_c, touch_date, collection_c, touch_user, arnoteid_c )
SELECT dbo_IDgenerator.cluniqueID, dbo_IDgenerator.clientcode_c, dbo_IDgenerator.firstname_vc, dbo_IDgenerator.lastname_vc, dbo_IDgenerator.create_dt, dbo_IDgenerator.createuser_c, dbo_IDgenerator.create_dt, "n" AS collection_c, dbo_IDgenerator.createuser_c, dbo_IDgenerator.cluniqueID AS arnoteid_c
FROM dbo_IDgenerator
WHERE (((dbo_IDgenerator.createstatus)="SMF-Pending"));

you can see what i'm trying to achive. the simple query works but i would like to use a SQL insert instead.
0
 
bootyfreakkAuthor Commented:
Hey matthewspatrick
It worked like a charm. Thanks your your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now