How Can Users Without Access Do Data Entry

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

I need suggestions on possible ways to implement the following:

10 users on a network need to infrequently add data to a backend database on the
network. The data would be a couple of records containing maybe a half dozen
fields. The users do not have Access.

Would adding a form to the backend database for these users be a viable option
considering they would need to open the form over the network?

Thanks for all anticipated help!

Heather
 
You could link excel spreadsheets or tab separated text files

They are not very secure and your "users" would have to know what they were
doing but it's possible.....

Hope that helps,

John
 
I need suggestions on possible ways to implement the following:

10 users on a network need to infrequently add data to a backend
database on the network. The data would be a couple of records
containing maybe a half dozen fields. The users do not have Access.

Would adding a form to the backend database for these users be a viable
option considering they would need to open the form over the network?

Thanks for all anticipated help!

Heather
Click to expand...

I'd use an HTA-ADO application.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnie50/ht
ml/ie5hta.asp>

But, I'm guessing that not many others would.
 
Heather said:
I need suggestions on possible ways to implement the following:

10 users on a network need to infrequently add data to a backend database on the
network. The data would be a couple of records containing maybe a half dozen
fields. The users do not have Access.

Would adding a form to the backend database for these users be a viable option
considering they would need to open the form over the network?

Thanks for all anticipated help!

Heather
Click to expand...
Why don't you install IIS on the comp where Access is installed, and then
create a small ASP script to insert into database via HTML form?
The only thing needed is IIS, network and IP number of server.
 
Lyle,

Thank you for the response!

I read the article at the link you provided - I'd like to experiment with this.
Could you give me a brief outline on how to set it up; I can work out the code.
I presume I would need a HTML form for data entry. How do I create that? Then
how do I get the data to the backend table - I presume that is the ADO part.

Thanks for any help you can give me!

Heather
 
Lyle,

Thank you for the response!

I read the article at the link you provided - I'd like to experiment
with this. Could you give me a brief outline on how to set it up; I can
work out the code. I presume I would need a HTML form for data entry.
How do I create that? Then how do I get the data to the backend table -
I presume that is the ADO part.

Thanks for any help you can give me!

Heather
Click to expand...

This is a small hta app. It backs up an SQL db on its server, zips the
backup and downoads it to the user's machine. It does not require that ASP
be installed. While I generally do not use in-line styles, I have done so
here to achieve a one file utility. It requires Internet Explorer.

<html>
<head>
<title>
Some Title
</title>
<hta:application id="ohta"
applicationname="fnd"
border="thick"
borderstyle="ridge"
caption="yes"
icon=""
maximizebutton="yes"
minimizebutton="yes"
showintaskbar="no"
singleinstance="no"
sysmenu="yes"
version="1.0"
windowstate="maximize" />
<script language=javascript>
function executeSP(n,a){
var c=new ActiveXObject('ADODB.Connection');
var m=new ActiveXObject('ADODB.Command');
var e=new ActiveXObject('ADODB.Error');
var s=new String();
connect(c);
c.Errors.Clear();
initializeCommand(m,c,n,a);
m.Execute();
while(m.State&4);
if(c.Errors.Count==0){
alert(n+' executed successfully.')
}
else{
for(e in c.Errors){
s+='\n'+e.Description;
}
alert(s)
}
c.Close();
}
function connect(c){
var s=new String();
s+='Provider=sqloledb;';
s+='Data Source=255.0.255.0,9999;';
s+='Initial Catalog=BlahBlah;';
s+='User ID='+document.forms[0].userID.value+';'
s+='Network Library=DBMSSOCN;';
s+='Password='+document.forms[0].password.value+';'
c.ConnectionString=s;
c.CursorLocation=3;
c.Open();
}
function initializeCommand(m,c,n,a){
m.ActiveConnection=c;
m.CommandText=n;
m.CommandType=4;
m.Prepared=true;
if((typeof a=='object')&&(a.constructor==Array)){
for(z=0;z<a.length;z++){
if(a[z]){
m.Parameters(z+1).Value=a[z];
}
}
}
}
function zipFile(){
var shell=new ActiveXObject("Wscript.Shell")
shell.run('pkzip.exe C:\Webfiles\Folder\FNDBackup.bak
C:\Webfiles\Folder\FNDBackup.bak'); }

</script>
<body
style="
font-family:tahoma;
font-size:12px;
text-align:center;
filter:
progid:DXImageTransform.Microsoft.Gradient(gradientType=1,startColorStr=dar
kgreen,endColorStr=white); ">
<form>
<input
id=userID
name=userID
type=text<br>
User Id
<br>
<br>
<input
id=password
name=password
type=password<br>
Password
<br>
<br>
</form>
<button
onClick ="executeSP('spBackUPDB', Array(0))"
style="background-color:red; color:white; cursor:hand;
width:128px"
type=buttonCreate Backup
</button>
<br>
<br>
<button
onClick ="document.all.download.click()"
style="background-color:green; color:white; cursor:hand;
width:128px"
type=buttonDownload Backup
</button>
<br>
<br>
<button
onClick ="executeSP('spDropBackup', Array(0))"
style="background-color:blue; color:white; cursor:hand;
width:128px"
type=buttonDelete Backup
</button>
<a
id=download
name=download
href=http://some.net/FNDBackup.bak</a>
 
Thanks, Lyle,

I really appreciate the copy of your sample file!!

Heather


Lyle Fairfield said:
Lyle,

Thank you for the response!

I read the article at the link you provided - I'd like to experiment
with this. Could you give me a brief outline on how to set it up; I can
work out the code. I presume I would need a HTML form for data entry.
How do I create that? Then how do I get the data to the backend table -
I presume that is the ADO part.

Thanks for any help you can give me!

Heather
Click to expand...

This is a small hta app. It backs up an SQL db on its server, zips the
backup and downoads it to the user's machine. It does not require that ASP
be installed. While I generally do not use in-line styles, I have done so
here to achieve a one file utility. It requires Internet Explorer.

<html>
<head>
<title>
Some Title
</title>
<hta:application id="ohta"
applicationname="fnd"
border="thick"
borderstyle="ridge"
caption="yes"
icon=""
maximizebutton="yes"
minimizebutton="yes"
showintaskbar="no"
singleinstance="no"
sysmenu="yes"
version="1.0"
windowstate="maximize" />
<script language=javascript>
function executeSP(n,a){
var c=new ActiveXObject('ADODB.Connection');
var m=new ActiveXObject('ADODB.Command');
var e=new ActiveXObject('ADODB.Error');
var s=new String();
connect(c);
c.Errors.Clear();
initializeCommand(m,c,n,a);
m.Execute();
while(m.State&4);
if(c.Errors.Count==0){
alert(n+' executed successfully.')
}
else{
for(e in c.Errors){
s+='\n'+e.Description;
}
alert(s)
}
c.Close();
}
function connect(c){
var s=new String();
s+='Provider=sqloledb;';
s+='Data Source=255.0.255.0,9999;';
s+='Initial Catalog=BlahBlah;';
s+='User ID='+document.forms[0].userID.value+';'
s+='Network Library=DBMSSOCN;';
s+='Password='+document.forms[0].password.value+';'
c.ConnectionString=s;
c.CursorLocation=3;
c.Open();
}
function initializeCommand(m,c,n,a){
m.ActiveConnection=c;
m.CommandText=n;
m.CommandType=4;
m.Prepared=true;
if((typeof a=='object')&&(a.constructor==Array)){
for(z=0;z<a.length;z++){
if(a[z]){
m.Parameters(z+1).Value=a[z];
}
}
}
}
function zipFile(){
var shell=new ActiveXObject("Wscript.Shell")
shell.run('pkzip.exe C:\Webfiles\Folder\FNDBackup.bak
C:\Webfiles\Folder\FNDBackup.bak'); }

</script>
<body
style="
font-family:tahoma;
font-size:12px;
text-align:center;
filter:
progid:DXImageTransform.Microsoft.Gradient(gradientType=1,startColorStr=dar
kgreen,endColorStr=white); ">
<form>
<input
id=userID
name=userID
type=text<br>
User Id
<br>
<br>
<input
id=password
name=password
type=password<br>
Password
<br>
<br>
</form>
<button
onClick ="executeSP('spBackUPDB', Array(0))"
style="background-color:red; color:white; cursor:hand;
width:128px"
type=buttonCreate Backup
</button>
<br>
<br>
<button
onClick ="document.all.download.click()"
style="background-color:green; color:white; cursor:hand;
width:128px"
type=buttonDownload Backup
</button>
<br>
<br>
<button
onClick ="executeSP('spDropBackup', Array(0))"
style="background-color:blue; color:white; cursor:hand;
width:128px"
type=buttonDelete Backup
</button>
<a
id=download
name=download
href=http://some.net/FNDBackup.bak</a>
Click to expand...
 
RE/
Would adding a form to the backend database for these users be a viable option
considering they would need to open the form over the network?
Click to expand...

I *think* they'd still need MS Access installed on their 'puter - if only a
network install.

One viable option would be MS Excel or MS Word with a couple of buttons and some
DAO coding behind a workbook or a Word table.

I've only done it a couple of times a long time ago...but it seems to me to be
an under-used option in areas where virtually everybody pounds on Excel
spreadsheets all day and very few have experience with MS Access.
 
Pete,

Thanks for responding!

Could you provide some pseudo code on how to do it with Excel. I'd be
particularly interested in seeing how to append to the Access table using DAO.

Thanks!

Heather
 
RE/
Could you provide some pseudo code on how to do it with Excel. I'd be
particularly interested in seeing how to append to the Access table using DAO.
Click to expand...

I sent a sample MS Access DB and accompanying MS Excel spreadsheet via email.
If you didn't get them, let me know here and I'll send again.

Here's the code that is in the sample spreadsheet. What's missing
is the query and table that are in the DB.
--------------------
Sub peopleAdd()
1000 On Error GoTo peopleAdd_err

' PURPOSE: To add the people's names shown on spreadsheet to
' an MS Access DB and then to clear the newly-added
' names from the sheet
' NOTES: 1) This routine requires Tools|References|Microsoft DAO 3.6 Object
Library to be selected
' 2) The line numbers are not necessary - just a convenience when
debugging. Most VB programmers
' do not use them. OTOH I live and die by them.
' 3) We wrap the adds in a transaction so that if one thing fails,
nothing gets added to
' the DB or deleted from the sheet

1001 Dim thisSheet As Worksheet
Dim thisWS As DAO.Workspace
Dim peopleDB As DAO.database
Dim peopleRS As DAO.Recordset
Dim myQuery As DAO.QueryDef

Dim i As Long
Dim myTimeStamp As Variant
Dim myPeopleList As String
Dim transOpen As Boolean
Dim addCount As Long
Dim errCount As Long

Const myPath = "C:\Temp\DaoFromExcelTest.mdb"
Const firstPersonRow = 3
Const lastPersonRow = 32

Const lastNameCol = 7
Const firstNameCol = 8
Const middleNameCol = 9
Const errorCol = 10

Const nameMin = 2

1010 Set thisWS = DBEngine(0)
1011 Set thisSheet = Worksheets(1)
1012 Set peopleDB = thisWS.OpenDatabase(myPath)
1013 Set peopleRS = peopleDB.OpenRecordset("tblPerson", dbOpenDynaset,
dbAppendOnly)
1019 myTimeStamp = Now()

1020 With thisSheet 'Clear any previous errors
1021 For i = firstPersonRow To lastPersonRow
1022 .Cells(i, errorCol) = ""
1023 Next i
1029 End With

1030 With thisSheet 'Check for errors, abort the save any errors found
1031 For i = firstPersonRow To lastPersonRow
1032 If Len(.Cells(i, lastNameCol) & .Cells(i, firstNameCol) & .Cells(i,
middleNameCol)) > 0 Then
1033 If Len(.Cells(i, lastNameCol)) < nameMin Then
1034 .Cells(i, errorCol) = "* Name < " & Str(nameMin) & "
characters."
1035 errCount = addCount + 1
1036 End If
1037 End If
1038 Next i
1039 End With

1300 If errCount = 0 Then
1301 With thisSheet
1302 For i = firstPersonRow To lastPersonRow
1303 If Len(.Cells(i, lastNameCol) & .Cells(i, firstNameCol) &
..Cells(i, middleNameCol)) > 0 Then
1304 peopleRS.AddNew
1305 peopleRS!NameLast = .Cells(i, lastNameCol)
1306 peopleRS!NameFirst = .Cells(i, firstNameCol)
1309 peopleRS!NameMiddle = .Cells(i, middleNameCol)
1310 peopleRS!CreatedAt = myTimeStamp
1311 peopleRS.Update
1312 addCount = addCount + 1
1313 End If
1314 Next i
1319 End With

1340 If addCount = 0 Then
1341 MsgBox "Nobody was added. Did you type anybody in?", vbexclaimation,
"Oops!"
1349 Else
1359 Set peopleRS = Nothing

1510 Set myQuery = peopleDB.QueryDefs("qryPeopleByTimeStamp")
1511 With myQuery
1512 .Parameters("theTimeStamp") = myTimeStamp
1513 Set peopleRS = .OpenRecordset(dbOpenSnapshot, dbForwardOnly)
1519 End With

1520 With peopleRS
1521 If Not ((.BOF = True) And (.EOF = True)) Then
1522 Do Until .EOF = True
1523 If Len(myPeopleList) = 0 Then
1524 myPeopleList = !NameLast & ", " & !NameFirst & " " &
!NameMiddle
1525 Else
1529 myPeopleList = myPeopleList & vbCrLf & !NameLast & ", " &
!NameFirst & " " & !NameMiddle
1530 End If
1531 .MoveNext
1532 Loop
1533 MsgBox myPeopleList, vbOKOnly + vbInformation, "These People
Were Added"
1534 End If
1539 End With

1990 With thisSheet 'if we got this far, delete the entered names and
commit the transaction
1991 For i = firstPersonRow To lastPersonRow
1992 .Cells(i, lastNameCol) = ""
1993 .Cells(i, firstNameCol) = ""
1994 .Cells(i, middleNameCol) = ""
1995 Next i
1996 End With
1997 End If
1999 End If

peopleAdd_xit:
On Error Resume Next
peopleRS.Close
Set peopleRS = Nothing
Set peopleDB = Nothing
Set thisWS = Nothing
Set thisSheet = Nothing
Exit Sub

peopleAdd_err:
MsgBox "At Line " & Erl & ": Error# " & Err & " '" & Error$ & "'.", vbOKOnly,
"There's Trouble In River City!"
If transOpen = True Then
thisWS.Rollback
End If
Resume peopleAdd_xit
End Sub
 
Pete,

Would you please email the sample MS Access DB and accompanying MS Excel
spreadsheet to (e-mail address removed)

Thank you,

Heather
 
Back
Top