How Can Users Without Access Do Data Entry

[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

[John Ortt]

Re: How Can Users Without Access Do Data Entry

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


"Heather" <hamonroe@csedu cationalsystems .org> wrote in message
news:oT%yb.1165 $Qd6.106@newsre ad1.news.atl.ea rthlink.net...[color=blue]
> I need suggestions on possible ways to implement the following:
>
> 10 users on a network need to infrequently add data to a backend database[/color]
on the[color=blue]
> network. The data would be a couple of records containing maybe a half[/color]
dozen[color=blue]
> fields. The users do not have Access.
>
> Would adding a form to the backend database for these users be a viable[/color]
option[color=blue]
> considering they would need to open the form over the network?
>
> Thanks for all anticipated help!
>
> Heather
>
>[/color]

[Lyle Fairfield]

Re: How Can Users Without Access Do Data Entry

"Heather" <hamonroe@csedu cationalsystems .org> wrote in
news:oT%yb.1165 $Qd6.106@newsre ad1.news.atl.ea rthlink.net:
[color=blue]
> 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[/color]

I'd use an HTA-ADO application.
<http://msdn.microsoft.com/library/de...n-us/dnie50/ht
ml/ie5hta.asp>

But, I'm guessing that not many others would.


--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Juggernath]

Re: How Can Users Without Access Do Data Entry


"Heather" <hamonroe@csedu cationalsystems .org> wrote in message
news:oT%yb.1165 $Qd6.106@newsre ad1.news.atl.ea rthlink.net...[color=blue]
> I need suggestions on possible ways to implement the following:
>
> 10 users on a network need to infrequently add data to a backend database[/color]
on the[color=blue]
> network. The data would be a couple of records containing maybe a half[/color]
dozen[color=blue]
> fields. The users do not have Access.
>
> Would adding a form to the backend database for these users be a viable[/color]
option[color=blue]
> considering they would need to open the form over the network?
>
> Thanks for all anticipated help!
>
> Heather
>[/color]
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.

[Tom van Stiphout]

Re: How Can Users Without Access Do Data Entry

On Tue, 02 Dec 2003 12:49:24 GMT, "Heather"
<hamonroe@csedu cationalsystems .org> wrote:

No. In order for that to work they would need Access on their
machines.

You could build an Access application, and then distribute it to them
with the runtime version of Access.

Or you could write a web application.

-Tom.

[color=blue]
>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
>[/color]

[Heather]

Re: How Can Users Without Access Do Data Entry

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 Fairfield" <MissingAddress @Invalid.Com> wrote in message
news:Xns9445555 094344FFDBA@130 .133.1.4...[color=blue]
> "Heather" <hamonroe@csedu cationalsystems .org> wrote in
> news:oT%yb.1165 $Qd6.106@newsre ad1.news.atl.ea rthlink.net:
>[color=green]
> > 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[/color]
>
> I'd use an HTA-ADO application.
> <http://msdn.microsoft.com/library/de...n-us/dnie50/ht
> ml/ie5hta.asp>
>
> But, I'm guessing that not many others would.
>
>
> --
> Lyle
> (for e-mail refer to http://ffdba.com/contacts.htm)[/color]

[Lyle Fairfield]

Re: How Can Users Without Access Do Data Entry

"Heather" <hamonroe@csedu cationalsystems .org> wrote in
news:_b3zb.1305 $Qd6.1047@newsr ead1.news.atl.e arthlink.net:
[color=blue]
> 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 Fairfield" <MissingAddress @Invalid.Com> wrote in message
> news:Xns9445555 094344FFDBA@130 .133.1.4...[color=green]
>> "Heather" <hamonroe@csedu cationalsystems .org> wrote in
>> news:oT%yb.1165 $Qd6.106@newsre ad1.news.atl.ea rthlink.net:
>>[color=darkred]
>> > 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[/color]
>>
>> I'd use an HTA-ADO application.
>> <http://msdn.microsoft.com/library/de...y/en-us/dnie50
>> /ht ml/ie5hta.asp>
>>
>> But, I'm guessing that not many others would.
>>
>>
>> --
>> Lyle
>> (for e-mail refer to http://ffdba.com/contacts.htm)[/color][/color]

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:applicatio n id="ohta"
applicationname ="fnd"
border="thick"
borderstyle="ri dge"
caption="yes"
icon=""
maximizebutton= "yes"
minimizebutton= "yes"
showintaskbar=" no"
singleinstance= "no"
sysmenu="yes"
version="1.0"
windowstate="ma ximize" />
<script language=javasc ript>
function executeSP(n,a){
var c=new ActiveXObject(' ADODB.Connectio n');
var m=new ActiveXObject(' ADODB.Command') ;
var e=new ActiveXObject(' ADODB.Error');
var s=new String();
connect(c);
c.Errors.Clear( );
initializeComma nd(m,c,n,a);
m.Execute();
while(m.State&4 );
if(c.Errors.Cou nt==0){
alert(n+' executed successfully.')
}
else{
for(e in c.Errors){
s+='\n'+e.Descr iption;
}
alert(s)
}
c.Close();
}
function connect(c){
var s=new String();
s+='Provider=sq loledb;';
s+='Data Source=255.0.25 5.0,9999;';
s+='Initial Catalog=BlahBla h;';
s+='User ID='+document.f orms[0].userID.value+' ;'
s+='Network Library=DBMSSOC N;';
s+='Password='+ document.forms[0].password.value +';'
c.ConnectionStr ing=s;
c.CursorLocatio n=3;
c.Open();
}
function initializeComma nd(m,c,n,a){
m.ActiveConnect ion=c;
m.CommandText=n ;
m.CommandType=4 ;
m.Prepared=true ;
if((typeof a=='object')&&( a.constructor== Array)){
for(z=0;z<a.len gth;z++){
if(a[z]){
m.Parameters(z+ 1).Value=a[z];
}
}
}
}
function zipFile(){
var shell=new ActiveXObject(" Wscript.Shell")
shell.run('pkzi p.exe C:\Webfiles\Fol der\FNDBackup.b ak
C:\Webfiles\Fol der\FNDBackup.b ak'); }

</script>
<body
style="
font-family:tahoma;
font-size:12px;
text-align:center;
filter:
progid:DXImageT ransform.Micros oft.Gradient(gr adientType=1,st artColorStr=dar
kgreen,endColor Str=white); ">
<form>
<input
id=userID
name=userID
type=text[color=blue]
>[/color]
<br>
User Id
<br>
<br>
<input
id=password
name=password
type=password[color=blue]
>[/color]
<br>
Password
<br>
<br>
</form>
<button
onClick ="executeSP('sp BackUPDB', Array(0))"
style="backgrou nd-color:red; color:white; cursor:hand;
width:128px"
type=button[color=blue]
>[/color]
Create Backup
</button>
<br>
<br>
<button
onClick ="document.all. download.click( )"
style="backgrou nd-color:green; color:white; cursor:hand;
width:128px"
type=button[color=blue]
>[/color]
Download Backup
</button>
<br>
<br>
<button
onClick ="executeSP('sp DropBackup', Array(0))"
style="backgrou nd-color:blue; color:white; cursor:hand;
width:128px"
type=button[color=blue]
>[/color]
Delete Backup
</button>
<a
id=download
name=download
href=http://some.net/FNDBackup.bak[color=blue]
>[/color]
</a>


--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Heather]

Re: How Can Users Without Access Do Data Entry

Thanks, Lyle,

I really appreciate the copy of your sample file!!

Heather


"Lyle Fairfield" <MissingAddress @Invalid.Com> wrote in message
news:Xns9445811 16BC4AFFDBA@130 .133.1.4...[color=blue]
> "Heather" <hamonroe@csedu cationalsystems .org> wrote in
> news:_b3zb.1305 $Qd6.1047@newsr ead1.news.atl.e arthlink.net:
>[color=green]
> > 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 Fairfield" <MissingAddress @Invalid.Com> wrote in message
> > news:Xns9445555 094344FFDBA@130 .133.1.4...[color=darkred]
> >> "Heather" <hamonroe@csedu cationalsystems .org> wrote in
> >> news:oT%yb.1165 $Qd6.106@newsre ad1.news.atl.ea rthlink.net:
> >>
> >> > 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
> >>
> >> I'd use an HTA-ADO application.
> >> <http://msdn.microsoft.com/library/de...y/en-us/dnie50
> >> /ht ml/ie5hta.asp>
> >>
> >> But, I'm guessing that not many others would.
> >>
> >>
> >> --
> >> Lyle
> >> (for e-mail refer to http://ffdba.com/contacts.htm)[/color][/color]
>
> 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:applicatio n id="ohta"
> applicationname ="fnd"
> border="thick"
> borderstyle="ri dge"
> caption="yes"
> icon=""
> maximizebutton= "yes"
> minimizebutton= "yes"
> showintaskbar=" no"
> singleinstance= "no"
> sysmenu="yes"
> version="1.0"
> windowstate="ma ximize" />
> <script language=javasc ript>
> function executeSP(n,a){
> var c=new ActiveXObject(' ADODB.Connectio n');
> var m=new ActiveXObject(' ADODB.Command') ;
> var e=new ActiveXObject(' ADODB.Error');
> var s=new String();
> connect(c);
> c.Errors.Clear( );
> initializeComma nd(m,c,n,a);
> m.Execute();
> while(m.State&4 );
> if(c.Errors.Cou nt==0){
> alert(n+' executed successfully.')
> }
> else{
> for(e in c.Errors){
> s+='\n'+e.Descr iption;
> }
> alert(s)
> }
> c.Close();
> }
> function connect(c){
> var s=new String();
> s+='Provider=sq loledb;';
> s+='Data Source=255.0.25 5.0,9999;';
> s+='Initial Catalog=BlahBla h;';
> s+='User ID='+document.f orms[0].userID.value+' ;'
> s+='Network Library=DBMSSOC N;';
> s+='Password='+ document.forms[0].password.value +';'
> c.ConnectionStr ing=s;
> c.CursorLocatio n=3;
> c.Open();
> }
> function initializeComma nd(m,c,n,a){
> m.ActiveConnect ion=c;
> m.CommandText=n ;
> m.CommandType=4 ;
> m.Prepared=true ;
> if((typeof a=='object')&&( a.constructor== Array)){
> for(z=0;z<a.len gth;z++){
> if(a[z]){
> m.Parameters(z+ 1).Value=a[z];
> }
> }
> }
> }
> function zipFile(){
> var shell=new ActiveXObject(" Wscript.Shell")
> shell.run('pkzi p.exe C:\Webfiles\Fol der\FNDBackup.b ak
> C:\Webfiles\Fol der\FNDBackup.b ak'); }
>
> </script>
> <body
> style="
> font-family:tahoma;
> font-size:12px;
> text-align:center;
> filter:
> progid:DXImageT ransform.Micros oft.Gradient(gr adientType=1,st artColorStr=dar
> kgreen,endColor Str=white); ">
> <form>
> <input
> id=userID
> name=userID
> type=text[color=green]
> >[/color]
> <br>
> User Id
> <br>
> <br>
> <input
> id=password
> name=password
> type=password[color=green]
> >[/color]
> <br>
> Password
> <br>
> <br>
> </form>
> <button
> onClick ="executeSP('sp BackUPDB', Array(0))"
> style="backgrou nd-color:red; color:white; cursor:hand;
> width:128px"
> type=button[color=green]
> >[/color]
> Create Backup
> </button>
> <br>
> <br>
> <button
> onClick ="document.all. download.click( )"
> style="backgrou nd-color:green; color:white; cursor:hand;
> width:128px"
> type=button[color=green]
> >[/color]
> Download Backup
> </button>
> <br>
> <br>
> <button
> onClick ="executeSP('sp DropBackup', Array(0))"
> style="backgrou nd-color:blue; color:white; cursor:hand;
> width:128px"
> type=button[color=green]
> >[/color]
> Delete Backup
> </button>
> <a
> id=download
> name=download
> href=http://some.net/FNDBackup.bak[color=green]
> >[/color]
> </a>
>
>
> --
> Lyle
> (for e-mail refer to http://ffdba.com/contacts.htm)[/color]

[(Pete Cresswell)]

Re: How Can Users Without Access Do Data Entry

RE/[color=blue]
>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?[/color]

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.
--
PeteCresswell

[Heather]

Re: How Can Users Without Access Do Data Entry

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


"(Pete Cresswell)" <x@y.z> wrote in message
news:no5qsvsskg f3nuvngkim9hbji en8t2bttq@4ax.c om...[color=blue]
> RE/[color=green]
> >Would adding a form to the backend database for these users be a viable[/color][/color]
option[color=blue][color=green]
> >considering they would need to open the form over the network?[/color]
>
> 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[/color]
some[color=blue]
> 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.
> --
> PeteCresswell[/color]

[(Pete Cresswell)]

Re: How Can Users Without Access Do Data Entry

RE/[color=blue]
>
>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.[/color]

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|Reference s|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\DaoFro mExcelTest.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.OpenData base(myPath)
1013 Set peopleRS = peopleDB.OpenRe cordset("tblPer son", 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!NameLa st = .Cells(i, lastNameCol)
1306 peopleRS!NameFi rst = .Cells(i, firstNameCol)
1309 peopleRS!NameMi ddle = .Cells(i, middleNameCol)
1310 peopleRS!Create dAt = 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.QueryD efs("qryPeopleB yTimeStamp")
1511 With myQuery
1512 .Parameters("th eTimeStamp") = 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(myPeopleLis t) = 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
--------------------
--
PeteCresswell

[Kathy]

Re: How Can Users Without Access Do Data Entry

Pete,

Would you please email the sample MS Access DB and accompanying MS Excel
spreadsheet to rlaird@penn.com

Thank you,

Heather



"(Pete Cresswell)" <x@y.z> wrote in message
news:qrossv43gl 4hrqorm96mr5j6l npk9cv54m@4ax.c om...[color=blue]
> RE/[color=green]
> >
> >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[/color][/color]
DAO.[color=blue]
>
> 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|Reference s|Microsoft DAO 3.6[/color]
Object[color=blue]
> 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\DaoFro mExcelTest.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.OpenData base(myPath)
> 1013 Set peopleRS = peopleDB.OpenRe cordset("tblPer son", 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[/color]
found[color=blue]
> 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!NameLa st = .Cells(i, lastNameCol)
> 1306 peopleRS!NameFi rst = .Cells(i, firstNameCol)
> 1309 peopleRS!NameMi ddle = .Cells(i, middleNameCol)
> 1310 peopleRS!Create dAt = 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?",[/color]
vbexclaimation,[color=blue]
> "Oops!"
> 1349 Else
> 1359 Set peopleRS = Nothing
>
> 1510 Set myQuery = peopleDB.QueryD efs("qryPeopleB yTimeStamp")
> 1511 With myQuery
> 1512 .Parameters("th eTimeStamp") = 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(myPeopleLis t) = 0 Then
> 1524 myPeopleList = !NameLast & ", " & !NameFirst & " " &
> !NameMiddle
> 1525 Else
> 1529 myPeopleList = myPeopleList & vbCrLf & !NameLast & ", "[/color]
&[color=blue]
> !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
> --------------------
> --
> PeteCresswell[/color]