Writing to Access from Word

D

dsc

I'm having a lot of trouble finding out how to tack new records onto the end
of an .mdb file and write to it.

Any assistance gratefully received.
 
M

Malcolm Smith

How are you attempting to do this? Are you using ADO or whatever?

This is a sample bit of code I use to update one of the tables in one of
my racing databases. The indentation is all to pot because bits were in
one routine and bits were in another. But there is enough here to get
you going.

Remember to reference the Active-X Data Objects library.

Regards
Malc
www.dragondrop.com / www.ukhorseracing.co.uk



Dim conSelectionDB As ADODB.Connection
Dim RS As ADODB.Recordset


set conSelectionDB = ADODB.Connection
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sDatabasePath

If Len(sConnection) > 0 Then
' Remove the time out
objConnection.ConnectionTimeout = 0
objConnection.ConnectionString = sConnection
objConnection.open
bRetVal = True
Else
MsgBox "Unable to open database connection.", _
vbOKOnly + vbExclamation, "Error"
End If


Set RS = New ADODB.Recordset

RS.open "Selections", conSelectionDB, _
adOpenDynamic, adLockPessimistic, adCmdTable
RS.AddNew
RS("Date") = sDate
RS("Meeting") = sMeeting
RS("DisplayTime") = sDisplayTime
RS("Time") = sTime
RS("Horse") = sHorse
RS("ForecastSP") = sForecastSP
RS("ElapsedDays") = nElapsedDays
RS("RacingPostID") = nRacingPostID
RS.Update
RS.Close


set RS = nothing
conSelectionDB.Close
Set conSelectionDB = Nothing
 
D

dsc

Thanks for the reply, Malcolm.

Pardon my iggernunce, but when I get to this line:
set conSelectionDB = ADODB.Connection
Click to expand...

I get an error: Compile error: user-defined type not defined

I added Microsoft DAO 3.6 Object Library to my project, and already had
Microsoft Access 9.0 object library.

Is there some other reference I need?
 
D

dsc

Oh, and when I add Microsoft ActiveX Data Objects 2.1 Library to my project,
it hangs on the same line, but only highlights the word "connection'.
That error is Compile error: Method or data member not found

I really appreciate your help.
 
D

dsc

errr, how?

Umm...in a state of near-total ignorance?

I don't even know enough to know how to answer that.

I got some code from Malcolm, and am trying to make it work, but am hanging
up on the first Dim statement.

Dim statement. Dim.

Hmmm. I wonder if there's a clue there. Maybe I should go stand by the "You
must be at least this smart to use Basic" scale and see if that's the
problem.

What I want to do is create a new record in an existing .mdb file and read
three strings into three fields, triggered by a user form command button.

Anything that might tend to reduce the sum total of my ignorance gratefully
accepted.
 
M

Malcolm Smith

Sorry, my mistake.

I missed out a word:


set conSelectionDB = NEW ADODB.Connection


This is what happens when one copies code from all over the place.
Normally the code to grab the connection lives in a seperate routine to
make sure that the connection is not open.

Apologies
Malc
www.dragondrop.com
 
D

dsc

Apologies? Don't be silly. If somebody hadn't helped me I'd still be sitting
here braiding belly-button lint.

I am having another problem, though, with

objConnection.ConnectionTimeout = 0

I get an "Object required" error.

I'm wondering, do I need to declare objConnection, sDatabasePath, and
sConnection?
 
M

Malcolm Smith

Um, yes. Didn't I put them at the top of the code in Dim statements?

objConnection is an ADODB.Connection object
sDatabasePath is a string which is the location of the database.
sConnection is also a string which is used to create the objConnection.

Let me send you some code of one of my routines as I am obviously making a
mess here.

I use this code to create the objConnection object. One passes in an
objConnection object which is created elsewhere and also the path to the
database.

I do it this way because I want to make sure that I am not trying to use
an already opened database connection.

For example:

Dim sDatabase As String
Dim conDisplayDB As ADODB.Connection

sDatabase = recDatabase.sDisplayResultsDatabase
If CreateDatabaseConnection(conDisplayDB, sDatabase) Then


End If




Private Function CreateDatabaseConnection( _
ByRef objConnection As ADODB.Connection, _
ByVal sDatabasePath As String) As Boolean

Dim sConnection As String
Dim bRetVal As Boolean


On Error GoTo Error_OpenDatabaseConnection

bRetVal = False


If Not objConnection Is Nothing Then
' Connection object is still valid
bRetVal = True
GoTo Exit_OpenDatabaseConnection
End If


Set objConnection = New ADODB.Connection


sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sDatabasePath


If Len(sConnection) > 0 Then
' Remove the time out
objConnection.ConnectionTimeout = 0
objConnection.ConnectionString = sConnection
objConnection.open
bRetVal = True
Else
MsgBox "Unable to open database connection.", _
vbOKOnly + vbExclamation, "Error"
End If




Exit_OpenDatabaseConnection:

On Error Resume Next
CreateDatabaseConnection = bRetVal

Exit Function


Error_OpenDatabaseConnection:

bRetVal = False
MsgBox Str$(Err.Number) & ": " + Err.Description, _
vbOKOnly + vbExclamation, "Database open error"

Resume Exit_OpenDatabaseConnection

End Function



What happens here is that in the calling code I want to make sure that the
connection is opened. If it is not already open (it shouldn't happen, but
I like to make sure) then it will create a database connection pointing to
the location of the database held within the sDatabase string.

This string holds the location of the database. In my code you will see
that I have a record structure, as it happens, this is populated at the
start time from the registry with the database locations.

I am drifting off-topic here, but the idea is that you can see that I have
a connection object and a variable containing the database. I throw these
into the OpenDatabaseConnection() function and then I end up with a fully
working conDisplayDB connection object.

Then, as in previous mails, I work on that.

Have I confused you even more? If so, I will start again.

Malc
www.dragondrop.com
 
D

dsc

Malcolm,

Well, I guess I'm dumber than people think I look. I can never even get past
the first line of code.

I think the thing to do is call the private function
CreateDatabaseConnection(conDisplayDB, sDatabase) from a private sub
containing the code to write to the database, which is what I have tried to
do below. Now, in C, I could just pass the function call and the arguments,
but Basic insists that there has to be an " = " after the function call. I
have tried every variable and object in this code, and always get an error,
"Function call on left-hand side of assignment must return Variant or
Object."

Probably there's something so obvious that no one could imagine anyone
doesn't know it, only I don't.

If you haven't lost patience, I'd really appreciate it if you'd tell me what
I'm doing wrong here.

Thanks for everything.

Private Sub WriteToDataBase()

Dim bRetVal As Boolean
Dim objConnection As ADODB.Connection
Dim conSelectionDB As ADODB.Connection
Dim RS As ADODB.Recordset
Dim sDatabase As String
Dim conDisplayDB As ADODB.Connection
Dim sConnection As String
Dim sDatabasePath As String

'sConnection = "C:\DataBase.mdb"
'bRetVal = False
'sDatabasePath = "C:\DataBase.mdb"

CreateDatabaseConnection(conDisplayDB, sDatabase) = ADODB.Connection '
<-----------------#
'CreateDatabaseConnection(conDisplayDB, sDatabase) = True '
<-----------------#
'CreateDatabaseConnection(conDisplayDB, sDatabase) = False '
<-----------------#
'CreateDatabaseConnection(conDisplayDB, sDatabase) = bRetVal '
<-----------------#
'CreateDatabaseConnection(conDisplayDB, sDatabase) = objConnection '
<-----------------#
'CreateDatabaseConnection(conDisplayDB, sDatabase) = conSelectionDB '
<-----------------#
'CreateDatabaseConnection(conDisplayDB, sDatabase) = RS '
<-----------------#
'CreateDatabaseConnection(conDisplayDB, sDatabase) = conDisplayDB '
<-----------------#
'CreateDatabaseConnection(conDisplayDB, sDatabase) = sConnection '
<-----------------#
CreateDatabaseConnection(conDisplayDB, sDatabase) = sDatabasePath '
<-----------------#

Set conSelectionDB = ADODB.Connection
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sDatabasePath

If Len(sConnection) > 0 Then
' Remove the time out
objConnection.ConnectionTimeout = 0
objConnection.ConnectionString = sConnection
objConnection.Open
bRetVal = True
Else
MsgBox "Unable to open database connection.", _
vbOKOnly + vbExclamation, "Error"
End If

Set RS = New ADODB.Recordset

RS.Open "Selections", conSelectionDB, _
adOpenDynamic, adLockPessimistic, adCmdTable
RS.AddNew
RS("Field 1") = "Fiddle"
RS("Field 2") = "Faddle"
RS("Field 3") = "Fuddle"
RS.Update
RS.Close

Set RS = Nothing
conSelectionDB.Close
Set conSelectionDB = Nothing

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''
Private Function CreateDatabaseConnection( _
ByRef objConnection As ADODB.Connection, _
ByVal sDatabasePath As String) As Boolean

On Error GoTo Error_OpenDatabaseConnection

sDatabase = recDatabase.sDisplayResultsDatabase
If CreateDatabaseConnection(conDisplayDB, sDatabase) Then
End If
bRetVal = False

If Not objConnection Is Nothing Then
' Connection object is still valid
bRetVal = True
GoTo Exit_OpenDatabaseConnection
End If

Set objConnection = New ADODB.Connection

sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sDatabasePath

If Len(sConnection) > 0 Then
' Remove the time out
objConnection.ConnectionTimeout = 0
objConnection.ConnectionString = sConnection
objConnection.Open
bRetVal = True
Else
MsgBox "Unable to open database connection.", _
vbOKOnly + vbExclamation, "Error"
End If

Exit_OpenDatabaseConnection:

On Error Resume Next
CreateDatabaseConnection = bRetVal

Exit Function

Error_OpenDatabaseConnection:

bRetVal = False
MsgBox Str$(Err.Number) & ": " + Err.Description, _
vbOKOnly + vbExclamation, "Database open error"

Resume Exit_OpenDatabaseConnection

End Function
 
M

Malcolm Smith

Hi!

What CreateDatabaseConnection() does is to return a boolean. If it is
true than all is working well.


So you would call it as such:



Dim oConnection as ADODB.Connection
dim RS as adodb.recordset
dim sDatabase as string

sDatabase = "C:\DataBase.mdb"

If CreateDatabaseConnection (oConnection, sDatabase) then

Set RS = New ADODB.Recordset

RS.Open "Selections", oConnection, _
adOpenDynamic, adLockPessimistic, adCmdTable
RS.AddNew
RS.Fields("Field 1") = "Fiddle"
RS.Fields("Field 2") = "Faddle"
RS.Fields("Field 3") = "Fuddle"
RS.Update
RS.Close

Set RS = Nothing
oConnection.Close
Set oConnection = Nothing

end if


Like C or any other language a funtion returns something and, thus the
return value must be handled.

In your code you had the CreateDatabaseConnection() call being given a
value rather than handling the return code. Also you had a string called
sDatabase which wasn't defined and also didn't contain anything as it was
in sDataBaseName.

Does that work now?

- Malc
www.dragondrop.com
 
D

dsc

Malcolm,

I'm still working like a chump, but the code is working like a champ.

I can't tell you how grateful I am.

Hope I get a chance to return the favor sometime.

Dave from Yokohama
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Drop down list control value in vba 2
M.S. Word 365 1
I have lost 6 months work, how can i get my file back 1
Working on birthday "book" for my wife and having layout/editing trouble 0
Internal Data Integrity (Type 4) Details Needed 1
VBA Coding Help for Beginner 0
Multi-Selection with Hand-writing draw tool 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0

Top