DODB.Connection help (1 Viewer)

[iain1978]

Hi,

I've looked thought the forums to see if I can get help with this but no luck so far.

Basically I have got some code that will take data from a word form although it wont run even though it appears correct. From what I can see I presume I didn't have the correct references enabled, but from what I've read I have. Currently I have the below references turned on.

Visual Basic for application
M/S Access 11.0 Object Library
M/S Word 11.0 Object Library
M/S DAO 3.6 Object Library

The code is basically

Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling

strDocName....ect ect

It is the blue bit that it is falling over. Can anyone help I presume it is simple but I just cant get it to work...The error I'm get is User-defined type not defined

 

[KeithG]

Do you have a reference set to Microsoft ActiveX Data Objects 2.7?

 

[iain1978]

thanks that got it...sometime you cant see the woods for the trees..

 

[iain1978]

Bloody trees all a bit overtated if you ask me. arrrghhhh

I'm now getting a new error and anyone that can help me I would be grateful. First of all here is the full code to what I'm trying to do.

Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling

strDocName = "C:\" & _
InputBox("Enter the name of the Word document " & _
"you want to import:", "Import Contract")

Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\" & _
"RISKDBTest1.mdb;"
rst.Open "tblRisk", cnn, _
adOpenKeyset, adLockOptimistic[/COLOR][/COLOR]
With rst
.AddNew
!Scope = doc.FormFields("fldNCimpact").Result
!Description = doc.FormFields("flddetails").Result
!Identfiedby = doc.FormFields("fldraisedby").Result
!OwnerofRisk = doc.FormFields("fldNCowner").Result
!Ownerofactions = doc.FormFields("fldDropdown2").Result
!ResoultionPlan = doc.FormFields("fldclosesteps").Result
!RiskTitle = doc.FormFields("fldProjectRefrence").Result
!Date = doc.FormFields("flddateraised").Result
!Dateassignedtoriskowner = doc.FormFields("fldNCownerdate").Result
!Dateassignedtoactionowner = doc.FormFields("fldreviewdate").Result
!policynotcompliedwith1 = doc.FormFields("fldPolicy1").Result
!policynotcompliedwith2 = doc.FormFields("fldPolicy2").Result
!policynotcompliedwith3 = doc.FormFields("fldPolicy3").Result
!posoass = doc.FormFields("fldpoandsoasses").Result
!NCdetails = doc.FormFields("NCimpact").Result
!Divsignoof = doc.FormFields("fldDivsignoff").Result
!groupsignoff = doc.FormFields("fldPGroupsignoff").Result
!groupsignoffdate = doc.FormFields("fldGroupapprdate").Result
!divsignodate = doc.FormFields("flddivapprodate").Result
!posoass = doc.FormFields("fldpoandsoasses").Result
!reasonfornc = doc.FormFields("reason").Result
!Waiveroracceptance = TNC.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "TNC Imported!"

Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application")
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
& "No data imported.", vbOKOnly, _
"Document Not Found"
Case 5941
MsgBox "The document you selected does not " _
& "contain the required form fields. " _
& "No data imported.", vbOKOnly, _
"Fields Not Found"
Case Else
MsgBox Err & ": " & Err.Description

End Select
GoTo Cleanup
End Sub

Whenever I run this code I get the error -2147217900: Invalid SQL statment; expected 'DELETE','INSERT','SELECT' OR 'UPDATE.

Any ideas where I'm going wrong.

 

[boblarson]

Are you trying to connect to a database outside of the one this code is in?

 

[iain1978]

The idea for this DB is to pick up data from a word form. It's really to stop cutting and pasting from word to access.

Ideally what I'm waiting to have is a button on my access form that collects the data contained within the word form from a given file path and create a suitable new record on my DB.

Hope that's makes sense.

 

[boblarson]

You don't need the connection you are trying to build then.

Just use

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst.Open "tblRisk", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

 

[boblarson]

I believe you also were running into problems since you were trying to add a new record, but had selected adKeyset as the connection type.

 

[KeithG]

Also I believe you need a .Update to commit the record to the table.

 

[boblarson]

Also I believe you need a .Update to commit the record to the table.

True, True...

 

[iain1978]

First off all thanks for your help so far. If it wasn't for this forum my DB couldn't do half of what it does.

I am still, however, running into the same error. It may just be my lack of knowledge so again pointers in the right direction would be helpful. From your previous advice the start of my code looks like the below. I still get the same error of invalid SQL statement. From running the code line at a time it appears to be the rst.open that is causing the error. Any ideas?

Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling

strDocName = "C:\" & _
InputBox("Enter the name of the Word document " & _
"you want to import:", "Import Contract")

Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)
Set rst = New ADODB.Recordset


cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\" & _
"RISKDBTEST1.mdb;"
rst.Open "tblRisk", CurrentProject.Connection, adOpenDynamic, adLockOptimistic...ect ect

 

[boblarson]

Are you sure that the table's name is actually tblRisk or do you also have a query and/or form named tblRisk (which can confuse Access)?

By the way, if you are using CurrentProject.Connection, you can get rid of this code: cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\" & _
"RISKDBTEST1.mdb;"

 

[iain1978]

thanks Bob. I have removed the code as suggested and low an behold yes my table name was spelt incorrectly.

I'm now getting a new error but I'll give it a bash at fixing it myself before bothering anyone again.

Thanks yet again