Recording Data from a VBA macro

[red6000]

Hi,

I have a macro with many userforms and have some code that records whether
each checkbox or optionbutton was selected. This data is then stored as a
string called MIDATA and added to a text file (and then this text file is
linked into an Access database)

The problem I have is that if 2 people run the code at exactly the same
file, the text file is ready-only. Is there a better way to record the data
in a file that can be accessed by multiple people. My code at the moment
is:

ChangeFileOpenDirectory _
"J:\MACRO MI\"
Documents.Open FileName:="MacroMI.txt"
Documents("MacroMI.txt").Activate
Selection.EndKey Unit:=wdStory
Selection.TypeParagraph
Selection.TypeText Text:=MIDATA
If ActiveDocument.ReadOnly = False Then
ActiveDocument.Save
End If
ActiveDocument.Close

I have thought about having some 'other' text files that the code can go to
if the main one is 'read-only', but this seems messey

Thanks for any help or pointers.

 

[Jezebel]

Write the data directly to the database. Access databases are mulituser (on
a small scale, at least).

 

[red6000]

Thanks Jezebel,

I'm new to Access, could you point me in the right direction on how I can
wrtie the data directly into the database?

Thanks again.

 

[Jezebel]

Start with Google. Search for SQL, VBA, ADO, and go from there.

 

[Doug Robbins - Word MVP]

See the last of the following series of articles:

Please Fill Out This Form
Part 1: Create professional looking forms in Word
http://www.computorcompanion.com/LPMArticle.asp?ID=22

Part 2: Adding Automation to your Word forms.
http://www.computorcompanion.com/LPMArticle.asp?ID=46

Part 3: Learn more VBA (macros) to automate your forms.
http://www.computorcompanion.com/LPMArticle.asp?ID=119

Part 4: Use custom dialog boxes in your Word forms
http://www.computorcompanion.com/LPMArticle.asp?ID=127

Part 5: Connect your AutoForm to a database to save input time and keep
better records!
http://www.computorcompanion.com/LPMArticle.asp?ID=136


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[red6000]

Thanks Doug, I'm trying to follow these but have hit a problem. My code is
below, but it is failing at the line (marked with the ****) with the error
'Invalid SQL statement; expected 'DELETE', 'Insert, 'procedure', 'select' or
'update'. Any ideas?

Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim vAccount As String
vConnection.connectionstring = "data source=J:\Macro MI.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vAccount = ParaChooserFrm.account
****vRecordSet.Open "Requirements MI", vConnection, adOpenKeyset,
adLockOptimistic
vRecordSet.AddNew

If vAccount <> "" Then
vRecordSet!account = vAccount
Else
vRecordSet!account = "."
End If

vRecordSet.Close
vConnection.Close

Set vRecordSet = Nothing
Set vConnection = Nothing