I'm trying to write to an Access database through VB

[RichardPrewitt]

I made a database in Access and have a program that writes to it made in Visual Basic. I am trying to get the vb program to write to two tables in the same form. For some reason I cannot get the fields of one record and write them to the fields of another table. I have a task table and a history table that basicaly have the same fields except for the completion date and of course the primary key. I need to transfer the data from the fields in the task table to the fields in the history table and calculate the completion date. I already have the completion date working, just not able to transfer the fields form the one table to the other.

 

[gol4]

how are you attempting to do this via a DAO or ADO recordset set or SQl

give code example of what you are trying.

 

[RichardPrewitt]

I am using two data controls. One linked to the task table of an access db and one linked to the history table of the access db. I want to take the text values from the task fields in the task table and make a new record in the history table by using the values from the task table.

Thanks for you time.

 

[gol4]

build a sql statement then use ado command object

sql example

strsql= "INSERT INTO target[(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression "

 

[foundryqa]

Richard,

One of the more simple ways to handle this would be through a macro. They are not my strong point, I like to do things in VBA, but if you are able to write a macro in the one Access db that does what you want, you can fire the macro from VB code. When we have updated imported tables in the past from VB.net, we will create an update macro in Access and then just fire the macro from vb code. Hope this helps,

Fred

 

[scottian]

use an append query.
set the criteria to link to either one or two fields on the form then append that record to your history table. trigger on some event either next record or close of the form

Be ALERT - Your country needs Lerts

 

[RichardPrewitt]

Great idea scottian, Is this going to be a query in Access or a SQL statement in VB? Whether it is in Access or VB can you give me a code example? I dont work with access very much except to strickly hold records. Just plain and simple no tweeks access tables. At the most a few indexes, and I do the bells and whisles in VB. So any help that you could give me as in examples or code and where to access it or put it would be GREATLY appreciated

Foundryqa thats a good idea also but Im trying to steer away from using macros because of update reasons. If there is an error in code from the update, I want to run a debug in vb and send them the new program, and not have to grab the db off the PC in another state (because the records will be different with each person using the db) to test the macros. But that is good thinking and if I cant get something in vb to work then I'll try that. Also if you could give me an example as to how I would do this in both, the macro in access and the trigger event in vb.

Thanks for the help all.

 

[RichardPrewitt]

Sorry for the double post but here is a little fyi to better understand my problem.

I have two tables with the same records. One is a task table that gets updated as the tasks get completed and assigned new due dates and the second table is pretty much static. All it does is take the values from the task table and writes a record for a history report. Now in Visual basic I have a completion form that has text boxes linked to the task table with a data control and have the same amount of label boxes linked to the history table with a different data control. I am wanting to take the values from the text boxes from the task table and put them in the label boxes on the history table. The way that I was attempting to do this is set the label.caption = to the text.text values. This does not work even though the values in the text fields are valid and error handleing is working perfectly.

Thanks again for all your help

 

[foundryqa]

Richard,

The code that another Tek-Tips member (Benlinkknilneb) made for me was the following:
Dim A As Object
A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase(db_pathname)
A.doCmd.RunMacro("macUpdateAll")

The macUpdateAll macro is simply a list of RunCode commands in the macro that fire various functions in vba.

Fred

 

[RichardPrewitt]

Wow learn something new everyday Can you help me out with some macros that would complete what I am trying to do. Basically take the fields of one table and put them in the fields of the second table. The field names are identical because all I did was copy the table with a different name (eg tasktable, historytable)

Thanks for your help all, even though I havent gotten it working correctly yet, you all have supplied me with some good leads on making this work

 

[foundryqa]

Richard,

At this point, you will want to follow Scottian's recommendation of an append query. I am not sure what you are using for criteria to determine which fields need to be moved from one table to the other, but your append and delete queries will need that info. The macro will be simple and contain a "RunQuery" command for the append query to take records from table (A) to table (B) and then one for a delete query to remove the records from table (A).
If the criteria for the append is the same for the delete, you should be set.

Fred

 

[RichardPrewitt]

OK YES!!!!! I figured a way to do it without changing the Access database.

I first had two data control one linked to the task table and the 2nd linked to the history table

I then made a label box for each field that I wanted to transfer to the history table and linked those to the task table.

Then I made a text box for each field that I wanted to recieve form the task table and linked those to the history table.

Since I am not going to perform calculations on the fields in the history table I then made a string variable for each field that I wanted to transfer then I set the variables equal to the caption property of the labels. Then I set the text property of the text boxes equal to the value of the variables.

http://WWWhhheeeewwwww

that was a lot of go around work.

I used label and text boxes to do this becuase it was easier for me to keep track of them and keep them seperate, making the label boxes visible for the user to see and the text boxes not visible just for place hlolders.

inbetween transfering the data over, I can do whatever calculations that are needed before I transfer the values.

Again, Thank you everyone for your help! Until my next stump, Good luck and Happy coding!