automate export

[Guest]

I want to update another access db table with the change and or new data I
have entered in toa separete db. How can I export this automatically when I
close the database.
Thanks for any advise and help,
Pixie

 

[Guest]

In an event procedure that executes just before you close the mdb you are in,
Create links to the tables in the other mdb you want update or append data
to. Then you will need to create update and append queries to perform those
actions. Then delete the links to the external mdb.

 

[Guest]

I would like to do this in a code so it will automatically update that other
table when I close the main database?
THank you
Pixie

 

[Guest]

My previous post describes the procedure for doing what you are trying to do.
Are you saying you need help with writing the code?

If so, you will need to provide a lot more information for anyone to be able
to do that.

1. How and where do you close your database?
2. How do you know what data has been added or modified that needs to be
sent to the target database?
3. Where is the target database - Is it in a folder you have rights to?
4. How proficient are you in VBA?

Please supply as much detail as possible and let's see if we can help you
with this task.

 

[Guest]

Questions please:
You wrote :Then you will need to create update and append queries to perform
those actions. I want to know do I create the queries in the main database or
the one you are updating? Then delete the links to the external mdb. Why
would I need to delete the links to the external db? can I not leave it
linked so that everytime I close the main db it will automatically update the
external db?

Another question would be should I update in another fashion? such as at the
end of the changes or new records that are input in to a form.

This database I have complete access to it. It is closed on a switchboard
form with a command button by using "CloseCurrentDatabase."

How do you know what data has been added or modified that needs to be sent
to the target database? I don't know I want to find this information and add
only what has been changed, so any help would be grateful.

And last but not least I am still learning VBA, so I would say I am a step
above a novice.

I hope this is what you were looking for.

THanks again for your help,
Pixie

 

[Guest]

Thanks for the info. As to Maintaining the links. That is fine, probably a
better approach.

Anything you do should be in the current database, not in the external
database.

Your newest idea to update the external database when you update your
current database would be much better. That way, you don't have to
complicate your world any more than necessary.

I don't know how you are updating data in your current datbase, but I will
assume you are using a bound form. I will also assume that no users change
any data in the external database except through the procedure you are
establishing here. My intent is to keep this as straightforward as possible.

Put some code in the After Update event of your form that will write the
data to the external table. What we will do, is look in the external table
to see if the record already exists. If it does, we will use an update, if
it does not, we will use an addnew.

Dim rstExternal as Recordset

'Open the table in the other database
Set rstExternal = Currentdb.OpenRecordset("ExternalTableName", _
dbOpenDynaset)
'Look for the record in the other database using a unique value
'First example is if the table field is test
'Second example is if it is numeric
rstExternal.FindFirst "[AKeyField] = '" & Me.txtKeyField & "'"
rstExternal.FindFirst "[AKeyField] = " & Me.txtKeyField

'See if we found it
If rstExternal.NoMatch Then ' It is a new one
rstExternal.AddNew
Else
rstExternal.Edit
End If

'Put the data in the fields based on you form's controls
rstExternal!SomeField1 = Me.txtSomeControl1
rstExternal!SomeField2 = Me.txtSomeControl2
.........
rstExternal!TheLastField = Me.txtSomeOtherControl
rstExternal.Update

Thats it.

 

[Guest]

Thank you very much. I will give this a try and get back with you.
Pixie

Thanks for the info. As to Maintaining the links. That is fine, probably a
better approach.

Anything you do should be in the current database, not in the external
database.

Your newest idea to update the external database when you update your
current database would be much better. That way, you don't have to
complicate your world any more than necessary.

I don't know how you are updating data in your current datbase, but I will
assume you are using a bound form. I will also assume that no users change
any data in the external database except through the procedure you are
establishing here. My intent is to keep this as straightforward as possible.

Put some code in the After Update event of your form that will write the
data to the external table. What we will do, is look in the external table
to see if the record already exists. If it does, we will use an update, if
it does not, we will use an addnew.

Dim rstExternal as Recordset

'Open the table in the other database
Set rstExternal = Currentdb.OpenRecordset("ExternalTableName", _
dbOpenDynaset)
'Look for the record in the other database using a unique value
'First example is if the table field is test
'Second example is if it is numeric
rstExternal.FindFirst "[AKeyField] = '" & Me.txtKeyField & "'"
rstExternal.FindFirst "[AKeyField] = " & Me.txtKeyField

'See if we found it
If rstExternal.NoMatch Then ' It is a new one
rstExternal.AddNew
Else
rstExternal.Edit
End If

'Put the data in the fields based on you form's controls
rstExternal!SomeField1 = Me.txtSomeControl1
rstExternal!SomeField2 = Me.txtSomeControl2
........
rstExternal!TheLastField = Me.txtSomeOtherControl
rstExternal.Update

Thats it.

Questions please:
You wrote :Then you will need to create update and append queries to perform
those actions. I want to know do I create the queries in the main database or
the one you are updating? Then delete the links to the external mdb. Why
would I need to delete the links to the external db? can I not leave it
linked so that everytime I close the main db it will automatically update the
external db?

Another question would be should I update in another fashion? such as at the
end of the changes or new records that are input in to a form.

This database I have complete access to it. It is closed on a switchboard
form with a command button by using "CloseCurrentDatabase."

How do you know what data has been added or modified that needs to be sent
to the target database? I don't know I want to find this information and add
only what has been changed, so any help would be grateful.

And last but not least I am still learning VBA, so I would say I am a step
above a novice.

I hope this is what you were looking for.

THanks again for your help,
Pixie