Access 2007 To Excel 2007 passing Parameter Part 2

Thank you all for helping me!

I have this code: A user signs on in Access it validates: If ok then it will change the Def of the Query/SQL "qryTitleA5".

 
sSQL = "Select * from MainTable where SCGNumber = '" & cboTemplateID & "' OR TemplateID = '" & cboTemplateID & "' AND  PassList ='" & Me.txtPassword & "'"

CurrentDb.QueryDefs("qryTitleA5").SQL = sSQL

Open in new window


But I need the query or SQL string in VB6 to do this:
 
SELECT "TITLE:" & "  " & " " & [MainTable].[Title] AS SCGA5
FROM MainTable;

Open in new window


Thank you all!
Amour22015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
What are the "  "  and " " For?

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Are you sure you don't mean this:

sSQL = "SELECT [MainTable].[Title] AS SCGA5 FROM MainTable;"

?
0
Amour22015Author Commented:
DatabaseMX:

Thank you for helping me!

I mean I am looking for the Query to be changed to the sSQL string:
somthing Like:
sSQL = "SELECT "TITLE:" & "  " & " " & [MainTable].[Title] AS SCGA5
FROM MainTable;

I know this is not a correct sSQL string. I am looking for the correct String?

But note the text in the string?  And I don't think I need the AS SCGA5?

Thank You!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Maybe this;

sSQL = "SELECT " & Chr(34) & "TITLE:" & "  " & " " & Chr(34) & " [MainTable].[Title] AS SCGA5 FROM MainTable;"

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK .. maybe this:

sSQL = "SELECT " & Chr(34) & "TITLE:" & "  " & " " & Chr(34) & " [Title] AS SCGA5 FROM MainTable"

which when executed becomes this:

SELECT "TITLE:   " [Title] AS SCGA5 FROM MainTable

and looks like the image in the query designer



Capture1.gif
Capture2.gif
0
Amour22015Author Commented:
DatabaseMX:

Thank you for helping me!

On this statement I get a syntax error:
sSQL = "SELECT " & Chr(34) & "TITLE:" & "  " & " " & Chr(34) & " [Title] AS SCGA5 FROM MainTable"


So I changed it to:
sSQL = "SELECT " & Chr(34) & "TITLE:" & "  " & " " & Chr(34) & " ([MainTable].[Title]) AS SCGA5 FROM MainTable"

I tryed with out the ( )?

I got by the syntax (but I don't think it is correct) because I got () error on the Excel sSQL string that call this query.

Please help and thank you!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, what I posted @ http:#a37361375 compiles and runs.  Did you try exactly that?
Is that output what you are trying to achieve ?

mx
0
Amour22015Author Commented:
DatabaseMX:

Thank you for helping me!

This is what I have:
 
Dim sSQLA5 As String
sSQLA5 = "SELECT " & Chr(34) & "TITLE:" & "  " & " " & Chr(34) & " [Title] AS SCGA5 FROM MainTable"
CurrentDb.QueryDefs("qryTitleA5").SQL = sSQLA5

Open in new window


I run this and get a syntax error:
Run-Time error '3075':
Syntax error (missing operator) in query expression '"TITLE: " [Title]'.

I get this error in Access.


Thank you!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ... try this - I thing an & was missing

sSQLA5 = "SELECT " & Chr(34) & "TITLE:" & "  " & " " & Chr(34) & " & [MainTable].[Title] AS SCGA5 FROM MainTable"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
DatabaseMX

Thank you,

Will got past the syntax error but getting the same error (As in the other post that you are trying to help me with Part3):
Run-time error '-2147217904(80040e10)':
No value given for one or more required parameters.

And that is in the Excel VBA side when it tries to get this query:
 
sSQLTitle = "Select SCGTitle from QryTitleA5"
Set rsTitle = New ADODB.Recordset
rsTitle.Open sSQLTitle, Conn
RngA5.CopyFromRecordset rsTitle

Open in new window


And that happens on this line:
rsTitle.Open sSQLTitle, Conn

Please help seems like it is really close to solving this problem?

Thank you!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
But how is this code related to sSQLA5 above ?

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK ... wait ... we have

sSQLA5 = "SELECT " & Chr(34) & "TITLE:" & "  " & " " & Chr(34) & " & [MainTable].[Title] AS SCGA5 FROM

So seem this:

sSQLTitle = "Select SCGTitle from QryTitleA5"

should be

sSQLTitle = "Select SCGA5 from QryTitleA5"
0
Amour22015Author Commented:
DatabaseMX

Thank you,

sSQLA5: In Access
Defines a query called QryTitleA5:
CurrentDb.QueryDefs("qryTitleA5").SQL = sSQLA5

Then Excel calls that query see post: 37361647

And I don't know if you have noticed yet, but that is how you read ONLY ONE RECORD in an open event on Excel VBA  See my posting Title: Access 2007 To Excel 2007 passing Parameter it is very interesting?
A5

otherwise like in your post: 37361375 image 2:
it shows all your records in the Table(reads all records):
In your case there are 5 records so in Excel I would get: all Cells populated.
A5
A6
A7
A8
A9

or what ever the amount of records are in the Table.

Thank you for helping me!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Can you put a breakpoint on that line and examine (post) what sSQLTitle actually is ?

Also, is it necessary to use ADO ?

mx
0
Amour22015Author Commented:
DatabaseMX

Thank you,


So that worked on this one.

I have one more question on this post and then we will be finished on this post:

 
SCGA23: "B.  " & " " & [MainTable].[Office] & " " & "is the cognizant Office responsible for " & " " & [MainTable].[Title]

Open in new window


I also need this in a sSQL string

Thank you.
0
Amour22015Author Commented:
DatabaseMX

Thank you,

Also, is it necessary to use ADO ?

I thought that ADO was the biggest/baddest/newest code out there for Access/Excel?  I am using ADO V6.0?  Also this is 2007 but will be going to 2010 64bit?

Thank You!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, if you wouldn't mind ... it's really one question per post.  How about closing this and opening a related Q ?

mx
0
Amour22015Author Commented:
Great Job and Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.