Apr 20 2004, 02:09 PM
I am new to ACCESS, however I could do this in SQL, infact if someone could tell me how to use the docmd.RunSQL command I would be fine. this is what I have.
I have a database named Marlin in the database I have 2 tables one called alldata and the other called todaydata, in the todaydata, I have the following fields, custID, Exchangerate, todaysdate, contactname, openticketdate, closeticketdate, openclose,
the form is setup as a datasheet view, I want to create a button that takes the information enter in the todaysdata and copies it into the alldata table, all the fields are the same, after the copy I want it to clear the todaydata table.
Also I need a Button that looks at the table alldata and brings over all the data to the todaysdata that is from the prior day.
I am at a total loss, can someone help?
I would do this in SQL like this
delete from dbo.Todaysdata
Apr 20 2004, 02:24 PM
To run sql in vb is pretty simple
strsql = "your statement here"
Apr 20 2004, 02:54 PM
Tried it, and it didnt work, this is what I got wrote
Private Sub Command14_Click()
strsql = "insert marlins.alldata (custid,exchangerate,[todays date],contactname,[openticketdate], closeticketdate,openclose) select custid,exchangerate,[todays date],contactname,[openticketdate], closeticketdate,openclose from marlins.todaydata"
ANd I get an error that states Run-Time error '3134': Syntax error in insert into statement
So I must be doing something wrong
Apr 20 2004, 03:52 PM
Oops, sorry I didn't look at the whole thing here.
For the first sql statement you should do it the way I said...
strsql = "insert into..."
At that point all you're doing is executing the statement. Remember for variables which you are porting into the system, you'll have to put together the string with something like this:
strsql = "insert into mytable (myfield1, myfield2) Values (" & myfield1 & ", " & myfield2 & ")"
For the second sql statement, you need to open a recordset. The recordset is what will store the results of your query.
dim rs as dao.recordset
dim strsql as string
strsql = "select * from [mytable]
set rs = currentdb.openrecordset(strsql)
Hope this helps.
Apr 21 2004, 07:53 AM
Once your SQL string is built, you can also use currentdb.execute strSQL to execute it.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here