|
Hi i'm trying to update a specific table in access by executing an append query.The code i'm using to do so is the following:
Dim SqlCmd As Command
Set SqlCmd = New Command
SqlCmd.CommandType = adCmdText
SqlCmd.ActiveConnection = MyCnn
SqlCmd.CommandText = "Execute CRegSubAdicNulo"
Set RecSet = SqlCmd.Execute(, , adExecuteNoRecords)
As far as i can see i don't see anything wrong,the problem is after executing that piece of code the table doesn't have anychanges
What am i doing wrong?
Thanks in advance!
|
|
|
|
|
Calferreira wrote: SqlCmd.ActiveConnection = MyCnn
Where are you instantiating the connection? (= New Connection)
I are troll
|
|
|
|
|
When the form loads a make a new instance from the connection:
Set Mycnn=New Connection
Mycnn.open blablabla
This 'CRegSubAdicNulo' is an append query made inside access that checks for records and inserts them into a table that will be edited to fill the empty columns.
|
|
|
|
|
Is there any error? What is inside MyCnn? Did you see if the connection is active first, are you able to read and write in other areas of your application?
|
|
|
|
|
I get no error the application runs fine,after executing this code i posted i can use the application normally getting data from the database with no problems.
|
|
|
|
|
Calferreira wrote: This 'CRegSubAdicNulo' is an append query made inside access that checks for records and ...
I guess your problem lies in CRegSubAdicNulo, you should probably start there.
|
|
|
|
|
If i run it manually inside access it runs fine with no problems.
It just displays a warning that the table will be modified and runs ok.
|
|
|
|
|
Well then, check to make sure your connection is established, thats all I can really think of.
|
|
|
|
|
Doing
Msgbox MyCnn.State
Gives me 1 as result so the connection is fine.
|
|
|
|
|
"Append"-queries are weird things. What happens when you try and use a "real" update-query?
I are troll
|
|
|
|
|
Sorry what do you mean by real?Using for exemple the sql code?
|
|
|
|
|
Open the query that your executing in MS-Access, in the "design view". You'll notice that it's an "append"-query, as you mentioned. These queries add the selected records to a table.
If you open the query in SQL-designview, you'll notice that it has a "SELECT INTO". Change the type to a "SELECT"-query - one that doesn't add records to a new table, but that just "selects", nothing more.
You may want to verify your SQL in that particular query
I are troll
|
|
|
|
|
Yup it runs fine.Shows all the records
|
|
|
|
|
If you run the query from VB.NET, as a selection-query (not the append-version), does it work too?
I are troll
|
|
|
|
|
I don't have VB.Net atm only vb6.
Don't know if it helps but the sql code in the query is the following:
INSERT INTO Substancias ( CODINTERNO, NOMESUBSTANCIA, NEC, NCAS, ESTADOFISICO, SECTOR, UTILIZAÇÃO, DENSIDADE, GRAUP, FRISCO, COV, CATSEVESO, QTARM, DATAFICHASEG, FORNECEDOR, ORIGEMFORN )
SELECT Artigos.CODIGO, Artigos.NOME, Substancias.NEC, Substancias.NCAS, Substancias.ESTADOFISICO, Substancias.SECTOR, Substancias.UTILIZAÇÃO, Substancias.DENSIDADE, Substancias.GRAUP, Substancias.FRISCO, Substancias.COV, Substancias.CATSEVESO, Substancias.QTARM, Substancias.DATAFICHASEG, Substancias.FORNECEDOR, Substancias.ORIGEMFORN
FROM Artigos LEFT JOIN Substancias ON Artigos.CODIGO = Substancias.CODINTERNO
WHERE (((Artigos.NOME) Like "acido*" Or (Artigos.NOME) Like "Oleo*" Or (Artigos.NOME) Like "cola*" Or (Artigos.NOME) Like "diluente*" Or (Artigos.NOME) Like "purpurina*" Or (Artigos.NOME) Like "tinta*" Or (Artigos.NOME) Like "verniz*"));
Funny thing is that i tried to execute the sql code directly and the result is the same nothing happens...
modified on Monday, January 12, 2009 10:44 AM
|
|
|
|
|
The question remains; if you run a "select"-query, that only returns those rows (without appending them), does it work?
I are troll
|
|
|
|
|
Yes it returns the rows without adding them to a new table.
|
|
|
|
|
Could it be that VB6 just doesn't like the "append"-type query?
Can you get the SQL-statement that is associated with your selection-query? If so, you could try and execute the 'raw' SQL. If that succeeds, add an "INSERT INTO" before the "SELECT"-statement.
Ehr.. one thing at a time - can you execute the query as an SQL-statement? Can you post a simplified version of the SQL here?
I are troll
|
|
|
|
|
I tried the raw sql command using only the select without the insert and shows nothing!The problem must be there!
|
|
|
|
|
Can you post the SQL statement that you used?
I are troll
|
|
|
|
|
"SELECT ARTIGOS.CODIGO, ARTIGOS.NOME " & _
"FROM ARTIGOS LEFT JOIN Substancias ON ARTIGOS.CODIGO = Substancias.CODINTERNO " & _
"WHERE (((ARTIGOS.NOME) Like 'Acido*' or 'Cola*' or 'Purpurina*'))"
Now i have another problem although i filter to some keywords as you can see it returns all rows.
Maybe this can only be solved with a Stored Procedure..
modified on Monday, January 12, 2009 11:42 AM
|
|
|
|
|
Umm just a little thing, I may be way off here but in SQL isn't the wildcard string %?
|
|
|
|
|
Already found the problem The problem was the sql query itself not it works but gives me a diferent error
The sql query i'm using now is the following:
INSERT INTO Substancias ( CODINTERNO, NOMESUBSTANCIA )
SELECT ARTIGOS.CODIGO, ARTIGOS.NOME
FROM ARTIGOS
WHERE (((ARTIGOS.NOME) Like "Cola*" Or (ARTIGOS.NOME) Like "Tinta*") AND ((Exists (SELECT * FROM SUBSTANCIAS WHERE ARTIGOS.CODIGO=SUBSTANCIAS.CODINTERNO))=False));
Much more simple
The problem is when i execute the following code :
Dim SqlCMD As Command
Set SqlCMD = New Command
SqlCMD.CommandText = "CRegSubAdicNulo"
SqlCMD.CommandType = adCmdStoredProc
SqlCMD.ActiveConnection = MyCnn
Set RecSet = SqlCMD.Execute(, , adExecuteNoRecords)
Gives the error "Few Parameters.1 Expected"
|
|
|
|
|
Sorry I don't know VB6 all that well.
|
|
|
|
|
Calferreira wrote: Set RecSet = SqlCMD.Execute(, , adExecuteNoRecords)
Try
Set RecSet = SqlCMD.Execute(adExecuteNoRecords)?
|
|
|
|