Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Marked Solved
Views: 4601

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Oct 1st, 2007

creating queries IN ACCESS using variables IN VB

Expand Post »

hi all, i have problem creating queries in access.this is because i do not know how to call the value from a variable in vb into the query in access.
let say in vb i've created :

(Toggle Plain Text)

Public booking_date As Date
Public fac_type As Integer
.
.
.
.
booking_date = MonthView1.value
fac_type = Combo1.ItemData

i suppose to have a query done in access which i will call in vb

(Toggle Plain Text)

SELECT TF.time_id
FROM TF
WHERE (((TF.fac_id)="fac_type") AND ((TF.reserv_date)="booking_date"));

how do i accomplish this?please help!
thanx in advnce!

Similar Threads

Creating an .exe using Access in MS Access and FileMaker Pro
How do I make my Access database work with MS SQL Server? in MS Access and FileMaker Pro
Using exisiting queries within MS Access in ASP.NET
How do I access variables in my queue? in C++
Creating data access layer in java in Java

nuBudDy

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Aug 2007

Permalink

Oct 1st, 2007

Re: creating queries IN ACCESS using variables IN VB

Click to Expand / Collapse Quote originally posted by nuBudDy ...

hi all, i have problem creating queries in access.this is because i do not know how to call the value from a variable in vb into the query in access.
let say in vb i've created :
(Toggle Plain Text)
Public booking_date As Date
Public fac_type As Integer
.
.
.
.
booking_date = MonthView1.value
fac_type = Combo1.ItemData
Public booking_date As Date Public fac_type As Integer . . . . booking_date = MonthView1.value fac_type = Combo1.ItemData
i suppose to have a query done in access which i will call in vb
(Toggle Plain Text)
SELECT TF.time_id
FROM TF
WHERE (((TF.fac_id)="fac_type") AND ((TF.reserv_date)="booking_date"));
SELECT TF.time_id FROM TF WHERE (((TF.fac_id)="fac_type") AND ((TF.reserv_date)="booking_date"));
how do i accomplish this?please help!
thanx in advnce!

Hi, i've attached a sample program. download it. hope it will give you some idea.
regards
Shouvik

Attached Files

Access.zip (12.4 KB, 78 views)

choudhuryshouvi

Reputation Points: 30

Solved Threads: 49

Posting Pro

Offline

553 posts
since May 2007

Permalink

Oct 3rd, 2007

Re: creating queries IN ACCESS using variables IN VB

thank you 4 the help choudry but what im trying to do is the reverse.instead of creating an sqlstatement in vb, i'd like to create query in access by using variables declared in vb.let say i code sql statement into query in access:

(Toggle Plain Text)

SELECT TF.time_id
FROM TF
WHERE (((TF.fac_id)="fac_type") AND ((TF.reserv_date)="booking_date"));

so when i'm done with this, i cant straight away call the query from access in vb.
the one in red is actually a variable declared in vb.
is this possible actually??
thanx in advance..

Last edited by nuBudDy; Oct 3rd, 2007 at 1:11 am. Reason: have some more information to add

nuBudDy

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Aug 2007

Permalink

Oct 3rd, 2007

Re: creating queries IN ACCESS using variables IN VB

Try this

vb Syntax (Toggle Plain Text)
select tf.time_id from tf where(((tf.rac_id= "'" fac_type & "') And ((" & tf.reserv_date)= " & booking-date & "))"
 
select tf.time_id from tf where(((tf.rac_id= "'" fac_type & "') And ((" & tf.reserv_date)= " & booking-date & "))"

Last edited by debasisdas; Oct 3rd, 2007 at 4:39 am.

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

Oct 3rd, 2007

Re: creating queries IN ACCESS using variables IN VB

debasisdas,
i tried but it keeps giving me a syntax error.could something be missing?do u mean to write :
fac_type as

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)

 ' " & fac_type & " '

and
booking_date as

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)

 ' " & booking_date & " '

nuBudDy

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Aug 2007

Permalink

Oct 4th, 2007

Re: creating queries IN ACCESS using variables IN VB

Click to Expand / Collapse Quote originally posted by nuBudDy ...

debasisdas,
i tried but it keeps giving me a syntax error.could something be missing?do u mean to write :
fac_type as
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
 ' " & fac_type & " '
' " & fac_type & " '
and
booking_date as
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
 ' " & booking_date & " '
' " & booking_date & " '
?

delete the trailing spaces from both side of the sql string. the correct syntax is:-

************for fac_type************
'" & fac_type & "'

************for booking_date************
'" & booking_date & "'

choudhuryshouvi

Reputation Points: 30

Solved Threads: 49

Posting Pro

Offline

553 posts
since May 2007

Permalink

Oct 4th, 2007

Re: creating queries IN ACCESS using variables IN VB

i still can't get it right..i wonder is it actually possible for the query to retrieve values from variables in vb at vb run-time..

nuBudDy

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Aug 2007

Permalink

Oct 4th, 2007

Re: creating queries IN ACCESS using variables IN VB

If you are writing vbcode in the MSAccess module (access macro), then you can access the variable from vb.

Also one more thing to be noted here is the data type of 'fac_type'. If it is an integer type you don't need to wrap the double quotes with single quotes
" & fac_type & " will do.
But if fac_type is a string value you need to give '" & fac_type & "'

Now, If you are writing vbcode outside access and connecting to access using ADO or DAO, I dont think you can directly access the variable from access query. The right way is to build the query in vb and pass it to access.

Hope this helps.

yello

Reputation Points: 13

Solved Threads: 4

Light Poster

Offline

30 posts
since Nov 2006

Permalink

Oct 7th, 2007

Re: creating queries IN ACCESS using variables IN VB

thank you very much for the infos.there's another thing i'm curious about.
usually,when we make an sql statement in vb,for an example :-

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)

Adodc1.RecordSource = SELECT * FROM Student WHERE student_id = '"& id & "'

in this case, we get a single set of information of the student(name,age,add,course,etc....).this is because the id is unique.

is it possible for "Adodc1.RecordSource" to hold more than one set of infos retrieved from the table?
eg : make a query and retrieve 3 rows of info matching the criteria of my sql statement.

*i tried doing it but keeps getting an error.i wonder if this might be the cause of my error.

nuBudDy

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Aug 2007

Permalink

Oct 7th, 2007

Re: creating queries IN ACCESS using variables IN VB

of course adodc can hold more than one row of criteria
do you have
adodc1.refresh
after
adodc1.recordsource
????

plusplus

Reputation Points: 10

Solved Threads: 16

Posting Whiz in Training

Offline

207 posts
since Jul 2007

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: plz help for encode

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: dynamic 2 dimensional array in vb 6

DaniWeb Message

Cancel Changes

User Name
Password