W
Wayne-I-M
Hi All
This gets asked quite a bit so I thought I would give one method. (sorry if
the english is not too good – sono Italiano)
I am "not" an expert with fronpage so you if there is anything you don't
understand (or I have got wrong) about the frontpage "bit" - post a question
and one of the experts may answer.
When you create a asp form in FP and ask the programme to save to database
it will create an access database and (normally) a table called “Resultsâ€
FP is “not†part of MS office so the data stored in the table (anything that
a user inputs onto a form) is not much use unless
1 You want to use this data to upgrade to functions of the on-line database
2 You want to use the data for something else (ie use the data "off-line")
Note that (as I said) FP is not part of office so you will need some codeing
to do this.
This is one way to do it – there are a few others but they need a little
more practice with the access DB programme and are a “little†bit more
complex.
So
I will assume you have a form which saves data to an online Access Database
(this is the “External†database – as the data is not stored on your hard
drive).
I will assume you have another database (on a hard drive) where you want the
results of the on-line form to be (eventually) – this is the “internalâ€
database.
So.
To import data from a table in an external database into (append) to a table
in your internal database.
The “cheats†I have given here you can either or not – I use them as I’m
lazy and – in this case – the end justifies the means (especially when the
means are delete
after the end is accomplished?)
_______________________________________
_______________________________________
_______________________________________
Oh – before you start – I would “strongly†suggest that you make a sample
form on a website and use this to practice on “1st†– up to you but it would
be a shame to lose “real†data that someone had put onto a online form.
_______________________________________
_______________________________________
_______________________________________
Next – before you start – import the on-line DB.
Open FP and log in, then click the FPDB, then the DB, then the blue arrow.
__________
Names – In this I have used….
External Database - This is where the records are now (from the online
form input)
The records are in a table called results
Internal Database - This is where I want them to be
tblInternalNewRecords - This the table in Internal Database where I want
the records go
-----------------------------------------
1 This is the 1st cheat – go to External Database and copy the table
(Results)
containing the data you want to bring in.
2 Go to Internal Database and paste the table (structure only). Call it
tblImportAppend
3 Past the same table again (structure only) Call it tblInternalNewRecords
So you now have 2 empty copies of the table results (but in your
Intenal Database) with new names.
4 Create a new query in your internal database (call it qryImportAppend).
Base this new query on tblImportAppend. Individually click each of the fields
so they all shown in the design grid.
5 Change the query to an Append Query. In the drop down list select
tblInternalNewRecords. If you have copied the tables correctly the fields in
the “Append To†row will fill in automatically. (You can change these update
to fields)
6 Create a new form in Internal Database.
7 Create a new button (call it cboImport)
8 Put this code on the OnClick event
Private Sub cboImport _Click()
Dim WayneIMPfile As TableDef
For Each WayneIMPfile In CurrentDb.TableDefs
If WayneIMPfile.Name = "tblImportAppend " Then
CurrentDb.TableDefs.Delete WayneIMPfile.Name
End If
Next
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to
ExternalDatabase.mdb", acTable, " results ", " tblImportAppend "
DoCmd.SetWarnings False
DoCmd.OpenQuery " qryImportAppend "
DoCmd.SetWarnings True
CurrentDb.TableDefs.Delete "tblImportAppend"
End Sub
Notes on this code
1 Change the names to whatever you want. - I wouldn’t have WayneIMPFile –
unless ….
2 The 1st section just looks for and deletes tblImportAppend if it’s there
3 The next bit DoCmd.TransferDatabase is a standard way to bring in tables
to a DB (press F1 in Access for more information)
4 The path to the MDB can be found by (another cheat).
Start – Run – Browse - Navigate to DB - Select open - Cut the path from the
run box and paste into code - Close run
5 Turn of warning
6 DoCmd.OpenQuery Runs the append query
7 Turn the warnings back on
8 Delete the table the code created (tblImportAppend)
9 End Sub – go and have a coffee
Note – if you look at the design of the Append Query it will look wrong - as
there is no table to base it on. Don’t worry this is normal. The code will
create the table, run the query then delete the table again (I tend to do
this as it’s just extra disk space).
Of course you can append the data to other tables and you don’t need to
append all fields. If you play around with the code and the Append you will
get it to do want you need.
I use this method quite often and have the validation process in the append
query – is text formatted correctly, are numbers right, are
postcodes/zipcodes
formated so the post office will accept them, etc, etc. But the most
important validation I use is not importing records that are already in the
table. You can use a time/date stamp (field) in the external table and in
the append simple put something like
Good luck
This gets asked quite a bit so I thought I would give one method. (sorry if
the english is not too good – sono Italiano)
I am "not" an expert with fronpage so you if there is anything you don't
understand (or I have got wrong) about the frontpage "bit" - post a question
and one of the experts may answer.
When you create a asp form in FP and ask the programme to save to database
it will create an access database and (normally) a table called “Resultsâ€
FP is “not†part of MS office so the data stored in the table (anything that
a user inputs onto a form) is not much use unless
1 You want to use this data to upgrade to functions of the on-line database
2 You want to use the data for something else (ie use the data "off-line")
Note that (as I said) FP is not part of office so you will need some codeing
to do this.
This is one way to do it – there are a few others but they need a little
more practice with the access DB programme and are a “little†bit more
complex.
So
I will assume you have a form which saves data to an online Access Database
(this is the “External†database – as the data is not stored on your hard
drive).
I will assume you have another database (on a hard drive) where you want the
results of the on-line form to be (eventually) – this is the “internalâ€
database.
So.
To import data from a table in an external database into (append) to a table
in your internal database.
The “cheats†I have given here you can either or not – I use them as I’m
lazy and – in this case – the end justifies the means (especially when the
means are delete
after the end is accomplished?)
_______________________________________
_______________________________________
_______________________________________
Oh – before you start – I would “strongly†suggest that you make a sample
form on a website and use this to practice on “1st†– up to you but it would
be a shame to lose “real†data that someone had put onto a online form.
_______________________________________
_______________________________________
_______________________________________
Next – before you start – import the on-line DB.
Open FP and log in, then click the FPDB, then the DB, then the blue arrow.
__________
Names – In this I have used….
External Database - This is where the records are now (from the online
form input)
The records are in a table called results
Internal Database - This is where I want them to be
tblInternalNewRecords - This the table in Internal Database where I want
the records go
-----------------------------------------
1 This is the 1st cheat – go to External Database and copy the table
(Results)
containing the data you want to bring in.
2 Go to Internal Database and paste the table (structure only). Call it
tblImportAppend
3 Past the same table again (structure only) Call it tblInternalNewRecords
So you now have 2 empty copies of the table results (but in your
Intenal Database) with new names.
4 Create a new query in your internal database (call it qryImportAppend).
Base this new query on tblImportAppend. Individually click each of the fields
so they all shown in the design grid.
5 Change the query to an Append Query. In the drop down list select
tblInternalNewRecords. If you have copied the tables correctly the fields in
the “Append To†row will fill in automatically. (You can change these update
to fields)
6 Create a new form in Internal Database.
7 Create a new button (call it cboImport)
8 Put this code on the OnClick event
Private Sub cboImport _Click()
Dim WayneIMPfile As TableDef
For Each WayneIMPfile In CurrentDb.TableDefs
If WayneIMPfile.Name = "tblImportAppend " Then
CurrentDb.TableDefs.Delete WayneIMPfile.Name
End If
Next
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to
ExternalDatabase.mdb", acTable, " results ", " tblImportAppend "
DoCmd.SetWarnings False
DoCmd.OpenQuery " qryImportAppend "
DoCmd.SetWarnings True
CurrentDb.TableDefs.Delete "tblImportAppend"
End Sub
Notes on this code
1 Change the names to whatever you want. - I wouldn’t have WayneIMPFile –
unless ….
2 The 1st section just looks for and deletes tblImportAppend if it’s there
3 The next bit DoCmd.TransferDatabase is a standard way to bring in tables
to a DB (press F1 in Access for more information)
4 The path to the MDB can be found by (another cheat).
Start – Run – Browse - Navigate to DB - Select open - Cut the path from the
run box and paste into code - Close run
5 Turn of warning
6 DoCmd.OpenQuery Runs the append query
7 Turn the warnings back on
8 Delete the table the code created (tblImportAppend)
9 End Sub – go and have a coffee
Note – if you look at the design of the Append Query it will look wrong - as
there is no table to base it on. Don’t worry this is normal. The code will
create the table, run the query then delete the table again (I tend to do
this as it’s just extra disk space).
Of course you can append the data to other tables and you don’t need to
append all fields. If you play around with the code and the Append you will
get it to do want you need.
I use this method quite often and have the validation process in the append
query – is text formatted correctly, are numbers right, are
postcodes/zipcodes
formated so the post office will accept them, etc, etc. But the most
important validation I use is not importing records that are already in the
table. You can use a time/date stamp (field) in the external table and in
the append simple put something like
(FP will create one for you in the Form Properties)
Good luck