By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,751 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Query Tools

P: 52
Hi...im doing a query tools using vb6 that runs a query in access, How can i do so....im confused...plz guide me....
Apr 24 '08 #1
Share this Question
Share on Google+
32 Replies


debasisdas
Expert 5K+
P: 8,127
You just need to frame the query properly and execute that using ADO objects.
Apr 24 '08 #2

P: 52
Im not understand..plzzzzzz guide..i'll thank 4 u
Apr 24 '08 #3

debasisdas
Expert 5K+
P: 8,127
Do you know how to execute a query in VB ?
Apr 24 '08 #4

P: 52
I had do the query n it was success . Thank u.Now i face difficulities in trim character from a coloumn like 101-001 to 101001. It can do by run a query in access directly but in vb6 i cant...

UPDATE Alternator
SET EversparkNo1 = REPLACE(EversparkNo1, '-', '')

this 1 i do in access query directly
Apr 25 '08 #5

jamesd0142
100+
P: 469
I had do the query n it was success . Thank u.Now i face difficulities in trim character from a coloumn like 101-001 to 101001. It can do by run a query in access directly but in vb6 i cant...

UPDATE Alternator
SET EversparkNo1 = REPLACE(EversparkNo1, '-', '')

this 1 i do in access query directly

You can run this query through vb also, you ned to execute a non query...
Apr 25 '08 #6

P: 52
what is non query? Plzzzzz give me some examples...urgent..thanks
Apr 28 '08 #7

debasisdas
Expert 5K+
P: 8,127
You can run this query through vb also, you ned to execute a non query...
What is a non query in vb 6.0 ???
Apr 28 '08 #8

P: 52
Do anyone can help me in solve this problem. I need to update my column using query.
Apr 28 '08 #9

jamesd0142
100+
P: 469
What is a non query in vb 6.0 ???
Does vb6 not have something simular to this debasisdas?, I use vb2005 and code such as:

Expand|Select|Wrap|Line Numbers
  1. imports system.data.oledb
  2.  
  3.                 Dim oConn As New System.Data.OleDb.OleDbConnection(StrConn)
  4.                 oConn.Close()
  5.                 Dim con As New System.Data.OleDb.OleDbCommand
  6.                 con.Connection = oConn
  7.                 con.CommandType = CommandType.Text
  8.                 con.CommandText = "Insert Statement"
  9.                 oConn.Open()
  10.                 con.ExecuteNonQuery()
  11.  
Surely you can do something like this in vb6, right...?
Apr 28 '08 #10

debasisdas
Expert 5K+
P: 8,127
There is nothing like non query in VB 6.0.

If you do not know the answer in the version that is clearly mentioned by the OP in the question, no need to post what you think is correct in higher version.

No need to post .NET replies to VB 6.0 questions.

Limit those to .NET forums only.
Apr 28 '08 #11

P: 52
Mine is vb6 platform. Plz do help me in correct query to update my field. Plsssssssss i still finding 4 the answer......
Apr 29 '08 #12

debasisdas
Expert 5K+
P: 8,127
try to use like this

Expand|Select|Wrap|Line Numbers
  1. con.BeginTrans                     'con is the ADODB connection object
  2. con.Execute "your sql query here"
  3. con.CommitTrans
  4.  
Apr 29 '08 #13

P: 52
Type mismatch error occur.Pls help me edit my coding down here.I need it urgently. Thanks in advance.....plzzzzzzzzz help me.


db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "Everspark2.mdb"

' Open a connection.
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
conn.Open

conn.BeginTrans
conn.Execute "UPDATE Alternator SET EversparkNo1 = REPLACE(EversparkNo1, " - ", "")"
conn.CommitTrans
Apr 29 '08 #14

P: 52
undefine function 'REPLACE' in expression error also occur.
Apr 29 '08 #15

P: 52
No body can help me???????????????plzzzzzzzzzzzz
Apr 30 '08 #16

debasisdas
Expert 5K+
P: 8,127
try this

Expand|Select|Wrap|Line Numbers
  1. conn.BeginTrans 
  2. conn.Execute "UPDATE Alternator SET EversparkNo1 = " & REPLACE(EversparkNo1, " - ", "")
  3. conn.CommitTrans
  4.  
Apr 30 '08 #17

P: 52
conn.BeginTrans
conn.Execute "UPDATE Alternator SET EversparkNo1 = " & Replace(EversparkNo1, " - ", "")
conn.CommitTrans

EversparkNo1 that i bold was not define eror....is it a variable? EversparkNo1 is a field in table. How to solve it. Plzzzzzzzz.....
May 2 '08 #18

debasisdas
Expert 5K+
P: 8,127
if that is a field then what is the meaning of your update statment ?
May 2 '08 #19

P: 52
i have one field which is Eversparkno1 where all its data got ' - ' now i want to remove the ' - ' to ' ' nothing, that means if 101-001 i want to change to 101001. so i want to do it by query, this replace successful if run directly in access but..now im doing in vb as a tools for query.
May 2 '08 #20

debasisdas
Expert 5K+
P: 8,127
try to use this

Expand|Select|Wrap|Line Numbers
  1. conn.BeginTrans
  2. conn.Execute "UPDATE Alternator SET EversparkNo1 = select Replace(EversparkNo1, " - ", "") from Alternator
  3. conn.CommitTrans
  4.  
NOTE :-- Check for the quotes ' and "
May 2 '08 #21

P: 52
Type dismatch error occur....plssssssss help me...next week i must present to my boss....

db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "Everspark2.mdb"

' Open a connection.
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
conn.Open

conn.BeginTrans
conn.Execute "UPDATE Alternator SET EversparkNo1 = SELECT REPLACE(EversparkNo1, " - ", "") FROM Alternator"
conn.CommitTrans
May 3 '08 #22

P: 52
Anyone can help me?????? Plssssssss
May 5 '08 #23

debasisdas
Expert 5K+
P: 8,127
Type dismatch error occur....
which line causes the error ?
May 5 '08 #24

P: 52
conn.Execute "UPDATE Alternator SET EversparkNo1 = SELECT REPLACE(EversparkNo1, " - ", "") FROM Alternator"

this line got error
May 6 '08 #25

debasisdas
Expert 5K+
P: 8,127
run the query directly in database and check if that works .
May 6 '08 #26

P: 52
No, the query shows error. This query down here is success.....

UPDATE Alternator SET EversparkNo1 = REPLACE(EversparkNo1, " - ", " ")
May 7 '08 #27

P: 52
why no one response to my question? plz help me ......i try a lot of way..but all error....
May 9 '08 #28

QVeen72
Expert 100+
P: 1,445
Hi,

Try This :

Expand|Select|Wrap|Line Numbers
  1. conn.Execute "UPDATE Alternator SET EversparkNo1 = REPLACE(EversparkNo1, ' -', '') "
  2.  

Regards
Veena
May 9 '08 #29

P: 52
I had try..but Undefine function "REPLACE" in expression error occur. Please Help me.
May 13 '08 #30

P: 57
I had try..but Undefine function "REPLACE" in expression error occur. Please Help me.

Hi Friend.

Please make sure that whether You have selected latest library or not regarding (DAO or ADO) so that library should support this function.

Please make sure that which version you are using.


Regards
Musai.
May 13 '08 #31

QVeen72
Expert 100+
P: 1,445
Hi,

Yes, "REPLACE" Function will not work with ADO,

Use a Combination of Left and MID and INSTR Functions..

Try This :

Expand|Select|Wrap|Line Numbers
  1. conn.Execute "UPDATE Alternator SET " _
  2. & " EversparkNo1 = Left(EversparkNo1,Val(InStr(EversparkNo1,'-')-1))  " _
  3. & " Mid(EversparkNo1,Val(InStr(EversparkNo1,'-')+1))  " _
  4. & " Where EversparkNo1 Like '%-%' "
  5.  

Note: Only First Found "-" is replaced.
If you have multiple occurance of the same character,
say 3 times , then you need to run the same query thrice...

Regards
Veena
May 13 '08 #32

QVeen72
Expert 100+
P: 1,445
Hi,

Sorry, "&" was removed.. Try this one:

Expand|Select|Wrap|Line Numbers
  1. conn.Execute "UPDATE Alternator SET " _
  2.   & " EversparkNo1 = Left(EversparkNo1,Val(InStr(EversparkNo1,'-')-1))  " _
  3.   & " & Mid(EversparkNo1,Val(InStr(EversparkNo1,'-')+1))  " _
  4.   & " Where EversparkNo1 Like '%-%' "
  5.  
Regards
Veena
May 13 '08 #33

Post your reply

Sign in to post your reply or Sign up for a free account.