Here is all the code for the SQL VBA issue

  • Thread starter Thread starter Brad Tornberg
  • Start date Start date
B

Brad Tornberg

Again the paste to a query works fine but when it is put
in VBA it returns 0 records.


Dim cnHistory As ADODB.Connection
Dim rsHistory As ADODB.Recordset
Dim Strsql As String

Set cnHistory = New ADODB.Connection
Set rsHistory = New ADODB.Recordset

cnHistory.ConnectionString = "driver={SQL Server};" & _
"server=2KMAIN;uid=sa;pwd=macola;database=DATA_300"
cnHistory.ConnectionTimeout = 30
cnHistory.Open

Strsql = "SELECT SFORDFIL_SQL.item_no,
SFORDFIL_SQL.ord_status, SFORDFIL_SQL.ord_type,
SFORDFIL_SQL.issue_loc, SFORDFIL_SQL.compl_dt,
SFDTLFIL_SQL.qty, SFDTLFIL_SQL.qty_issued,
SFDTLFIL_SQL.comp_item_no, IMITMIDX_SQL.user_amt FROM
(SFORDFIL_SQL INNER JOIN SFDTLFIL_SQL ON
SFORDFIL_SQL.ord_no = SFDTLFIL_SQL.ord_no) INNER JOIN
IMITMIDX_SQL ON SFDTLFIL_SQL.item_no =
IMITMIDX_SQL.item_no GROUP BY SFORDFIL_SQL.item_no,
SFORDFIL_SQL.ord_status, SFORDFIL_SQL.ord_type,
SFORDFIL_SQL.issue_loc, SFORDFIL_SQL.compl_dt,
SFDTLFIL_SQL.qty, SFDTLFIL_SQL.qty_issued,
SFDTLFIL_SQL.comp_item_no, IMITMIDX_SQL.user_amt HAVING
(((SFORDFIL_SQL.Item_No) like '" & mystr1 & "' ) And
((SFDTLFIL_SQL.comp_item_no) Like 'M*'))ORDER BY
SFORDFIL_SQL.compl_dt DESC;"

rsHistory.Open Strsql, cnHistory, , , adCmdText
 
I suspect that you may have a problem with your where
clause when you use Like.

When you run a query in Access in uses DAO and '*'
substitutes any character(s). When you open ADO connection
against SQL Server the equivalent of that will be '%'.
Instead you are searching for an exact string
SFDTLFIL_SQL.comp_item_no = 'M*'
which I think does not exist.

Try changing '*' to '%' and see whether this helps.
 
I hope that's not your real sa password you just posted, Brad. If it is, I
suggest you change it, like, yesterday.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top