Populating database

E

EjS

New Member
Joined
Jun 30, 2002
Messages
48
HI,

Not sure of the best way to do this-I have a database that will be populated with information from several sources and am looking for the best way to accomplish this. The respondents are outside my company-is it best to send a copy of the database and import the data, or maybe a data access page would work? The number of respondents might be 100+. Any help is greatly appreciated!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Couple of ways I can think of straight away.

1. Can you put the DB onto the web and get people to add there information using a HTML form

2. Send a spreadsheet and ask them to fill this out, then when you return it use data access to suck the info from specific cells into the DB. There are lots of different ways you could do this using a spreadsheet.

3. Email and ask them to return with there info. Easy but then you have the pain of entering the information.

My favourite is number 1 !

HTH

Chris
 
Upvote 0
Thanks Chris. A few questions about #1-would using an html form be similar or the same as a data access page? Would there be problems with uploading the data if several people access it at the same time?
 
Upvote 0
No, an HTML form is different to a data acces page. If several users tried to update at the same time you might get a poblem unless you used record locking.

Below is some ASP code I've used to update a guestbook which uses an HTML form and some ASP script to update a database.

HTH

Chris

==================================================
HTML FORM FILE
<HTML>
<HEAD>
<TITLE>Add Details</TITLE>
<SCRIPT LANGUAGE="JavaScript">
function validate() {

if (document.putdetails.uname.value.length <1) {
alert("Please enter a name");
return false;
}

return true;
}
</SCRIPT>

</HEAD>
<BODY>

<H1>Add your comments</H1>


Please type your comments in the boxes below and then use the submit button to add your
comments to the guest book. You can view my guest book also, go back to the Guest book page
and select the read link


<FORM name="putdetails" action="putguests.asp" method="post" onSubmit="return validate();">
<BLOCKQUOTE>
<TABLE border="0">
<TR>
<TD>Enter your Name
</TD>
<TD><INPUT TYPE="text" size="50" name="uname"></INPUT></TD>
</TR>
<TR>
<TD>Enter your e-mail Address
</TD>
<TD><INPUT TYPE="text" size="50" name="email"></INPUT></TD>
</TR>
<TR>
<TD>Enter your Favourite Page
</TD>
<TD><INPUT TYPE="text" size="20" name="fpage"></INPUT></TD>
</TR>
<TR>
<TD valign="top">Enter any comments you would like to make
</TD>
<TD><TEXTAREA TYPE="text" name="comments" rows="5" cols="40"></TEXTAREA></TD>
</TR>
</TABLE>
</BLOCKQUOTE>



<INPUT TYPE="submit" value="Submit your Comments">
<INPUT TYPE="reset" value="Reset Form">
</P>
</FORM>
</BODY>
</HTML>


=====================================================
ASP PAGE - putguests.asp


<HTML>
<HEAD>
<TITLE>Guest book - Add your comments</TITLE>
</HEAD>
<BODY>

<CENTER>
<H1><FONT size=4>
Thanks for your comments
<FONT></H1>

<%

dim MyConnection
dim rsTitle
dim rtnval

set MyConnection = server.createobject("ADODB.Connection")

MyConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=" & Server.Mappath("\MyDataBase.mdb")

putUname = request.form("uname")
putEmail = request.form("email")
putFpage = request.form("fpage")
putComments = request.form("comments")

v=1
strlength=len(putComments)
while v<strlength+1
if mid(putComments,v,1)="'" then
putComments=left(putComments,v-1) & " " & right(putComments, strlength-v)
end if
v=v+1
wend

SQLQuery = "insert into GuestBook values ('" & putUname &"','"& putEmail &"','"& putFpage &"','" & putComments & "')"

set rtnval = MyConnection.Execute(SQLQuery)
MyConnection.Close

%>
Back
</BODY>
</HTML>
 
Upvote 0
Whooops - sorry, forgot to disable HTML

No, an HTML form is different to a data acces page. If several users tried to update at the same time you might get a poblem unless you used record locking.

Below is some ASP code I've used to update a guestbook which uses an HTML form and some ASP script to update a database.

HTH

Chris

==================================================
HTML FORM FILE
<HTML>
<HEAD>
<TITLE>Add Details</TITLE>
<SCRIPT LANGUAGE="JavaScript">
function validate() {

if (document.putdetails.uname.value.length <1) {
alert("Please enter a name");
return false;
}

return true;
}
</SCRIPT>

</HEAD>
<BODY>

<H1>Add your comments</H1>


Please type your comments in the boxes below and then use the submit button to add your
comments to the guest book. You can view my guest book also, go back to the Guest book page
and select the read link


<FORM name="putdetails" action="putguests.asp" method="post" onSubmit="return validate();">
<BLOCKQUOTE>
<TABLE border="0">
<TR>
<TD>Enter your Name
</TD>
<TD><INPUT TYPE="text" size="50" name="uname"></INPUT></TD>
</TR>
<TR>
<TD>Enter your e-mail Address
</TD>
<TD><INPUT TYPE="text" size="50" name="email"></INPUT></TD>
</TR>
<TR>
<TD>Enter your Favourite Page
</TD>
<TD><INPUT TYPE="text" size="20" name="fpage"></INPUT></TD>
</TR>
<TR>
<TD valign="top">Enter any comments you would like to make
</TD>
<TD><TEXTAREA TYPE="text" name="comments" rows="5" cols="40"></TEXTAREA></TD>
</TR>
</TABLE>
</BLOCKQUOTE>



<INPUT TYPE="submit" value="Submit your Comments">
<INPUT TYPE="reset" value="Reset Form">
</P>
</FORM>
</BODY>
</HTML>


=====================================================
ASP PAGE - putguests.asp


<HTML>
<HEAD>
<TITLE>Guest book - Add your comments</TITLE>
</HEAD>
<BODY>

<CENTER>
<H1><FONT size=4>
Thanks for your comments
<FONT></H1>

<%

dim MyConnection
dim rsTitle
dim rtnval

set MyConnection = server.createobject("ADODB.Connection")

MyConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=" & Server.Mappath("\MyDataBase.mdb")

putUname = request.form("uname")
putEmail = request.form("email")
putFpage = request.form("fpage")
putComments = request.form("comments")

v=1
strlength=len(putComments)
while v<strlength+1
if mid(putComments,v,1)="'" then
putComments=left(putComments,v-1) & " " & right(putComments, strlength-v)
end if
v=v+1
wend

SQLQuery = "insert into GuestBook values ('" & putUname &"','"& putEmail &"','"& putFpage &"','" & putComments & "')"

set rtnval = MyConnection.Execute(SQLQuery)
MyConnection.Close

%>
Back
</BODY>
</HTML>
 
Upvote 0
Just how the hell do you disable the HTML !!!!!! tried the tick box but nothing worked!!!

To get the HTML in this substiture the $'s for < and > where appropriate.


No, an HTML form is different to a data acces page. If several users tried to update at the same time you might get a poblem unless you used record locking.

Below is some ASP code I've used to update a guestbook which uses an HTML form and some ASP script to update a database.

HTH

Chris

==================================================
HTML FORM FILE
$HTML$
$HEAD$
$TITLE$Add Details$/TITLE$
$SCRIPT LANGUAGE="JavaScript"$
function validate() {

if (document.putdetails.uname.value.length $1) {
alert("Please enter a name");
return false;
}

return true;
}
$/SCRIPT$

$/HEAD$
$BODY$

$H1$Add your comments$/H1$
$BR$
Please type your comments in the boxes below and then use the submit button to add your
comments to the guest book. You can view my guest book also, go back to the Guest book page
and select the read link
$BR$
$FORM name="putdetails" action="putguests.asp" method="post" onSubmit="return validate();"$
$BLOCKQUOTE$
$TABLE border="0"$
$TR$
$TD$Enter your Name$BR$$/TD$
$TD$$INPUT TYPE="text" size="50" name="uname"$$/INPUT$$/TD$
$/TR$
$TR$
$TD$Enter your e-mail Address$BR$$/TD$
$TD$$INPUT TYPE="text" size="50" name="email"$$/INPUT$$/TD$
$/TR$
$TR$
$TD$Enter your Favourite Page$BR$$/TD$
$TD$$INPUT TYPE="text" size="20" name="fpage"$$/INPUT$$/TD$
$/TR$
$TR$
$TD valign="top"$Enter any comments you would like to make$BR$$/TD$
$TD$$TEXTAREA TYPE="text" name="comments" rows="5" cols="40"$$/TEXTAREA$$/TD$
$/TR$
$/TABLE$
$/BLOCKQUOTE$
$P$
$INPUT TYPE="submit" value="Submit your Comments"$
$INPUT TYPE="reset" value="Reset Form"$
$/P$
$/FORM$
$/BODY$
$/HTML$


=====================================================
ASP PAGE - putguests.asp


$HTML$
$HEAD$
$TITLE$Guest book - Add your comments$/TITLE$
$/HEAD$
$BODY$

$CENTER$
$H1$$FONT size=4$
Thanks for your comments
$FONT$$/H1$

$%

dim MyConnection
dim rsTitle
dim rtnval

set MyConnection = server.createobject("ADODB.Connection")

MyConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=" & Server.Mappath("\MyDataBase.mdb")

putUname = request.form("uname")
putEmail = request.form("email")
putFpage = request.form("fpage")
putComments = request.form("comments")

v=1
strlength=len(putComments)
while v$strlength+1
if mid(putComments,v,1)="'" then
putComments=left(putComments,v-1) & " " & right(putComments, strlength-v)
end if
v=v+1
wend

SQLQuery = "insert into GuestBook values ('" & putUname &"','"& putEmail &"','"& putFpage &"','" & putComments & "')"

set rtnval = MyConnection.Execute(SQLQuery)
MyConnection.Close

%$
$A HREF="javascript:history.go(-1)"$Back$/A$
$/BODY$
$/HTML$
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Access Vba to copy table from one database to another
Replies
0
Views
3K
BalloutMoe
B
P
  • Question
Converting existing Excel Project Database over to Access
Replies
4
Views
1K
Micron
M
S
  • Question
Unusual Form Request
Replies
0
Views
838
Sean SR
S
K
  • Question
Pivot table conditional filtering?
Replies
0
Views
210
KimberlyWelty
K
A
  • Question
Take screenshots & simultaneously capture the link, place it into an Excel file which is linked to a FileMaker/Claris db.
Replies
3
Views
280
Dan_W
D

Forum statistics

Threads
1,221,631
Messages
6,160,942
Members
451,679
Latest member
BlueH1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top