Word to Access Import error 1

[cynaptic]

I have taken this code and tried to modify for a small project. I am not a VBA wizard and have come unstuck. The following code produces a compile error "type mismatch" - I don't know what that means, could someone please enlighten me and suggest a solution.

This seems to be the offending line:

strDocName = "C:\Contracts\" & _

TIA

Option Compare Database
Option Explicit

Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rst As DAO.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling

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

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

Set rst = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\My Documents\" & _
"SoftwareAcademyContacts.mdb;"
rst.Open "tblQuestionnaire", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!Name = doc.FormFields("Name&quot.Result
!Location = doc.FormFields("Location&quot.Result
!University = doc.FormFields("University&quot.Result
!Qualification = doc.FormFields("Qualification&quot.Result
!GraduationDate = doc.FormFields("GraduationDate&quot.Result
!Looking = doc.FormFields("Looking&quot.Result
!CurrentState = doc.FormFields("CurrentState&quot.Result
!CurrentStateExpand = doc.FormFields("CurrentStateExpand&quot.Result
!HearAbout = doc.FormFields("HearAbout&quot.Result
!HearAboutExpand = doc.FormFields("HearAboutExpand&quot.Result
!SAPurpose = doc.FormFields("SAPurpose&quot.Result
!ScottishOnly = doc.FormFields("ScottishOnly&quot.Result
!Connection = doc.FormFields("Connection&quot.Result
!ConnectionExpand = doc.FormFields("fldAdditional&quot.Result
!SADoneforme = doc.FormFields("SADoneforme&quot.Result
!RateService = doc.FormFields("RateService&quot.Result
!Methods = doc.FormFields("Methods&quot.Result
!MethodsExpand = doc.FormFields("MethodsExpand&quot.Result
!SuccesfulMethods = doc.FormFields("SuccesfulMethods&quot.Result
!SuccesfulMethodsExpand = doc.FormFields("SuccesfulMethodsExpand&quot.Result
!SubmittedCV = doc.FormFields("SubmittedCV&quot.Result
!Impression = doc.FormFields("Impression&quot.Result
!ImpressionExpand = doc.FormFields("ImpressionExpand&quot.Result
!Improvements = doc.FormFields("Improvements&quot.Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
MsgBox "Questionnaire Imported!"
Cleanup:
Set rst = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application&quot
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

 

[jonscott8]

First, add the following line of code with the other Dim statements:

Dim cnn as NEW ADODB.Connection

Next, change the following lines of code:

Dim rst As DAO.Recordset
Set rst = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\My Documents\" & _
"SoftwareAcademyContacts.mdb;"

to

Dim rst As NEW ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\My Documents\" & _
"SoftwareAcademyContacts.mdb;"

Then under your Cleanup subroutine, add:

Set cnn = Nothing Jon Hawkins

 

[cynaptic]

Hi John

Thanks for taking the time, however i should have mentioned that i am working in a 97 envirenment and thats why I was using DAO not ADO. I don't know the ins and outs but have been told that 97 prefers DAO to ADO.

Alex

 

[jonscott8]

OK. If you insist on using DAO, then your problem is you are using ADO syntax.

Set rst = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\My Documents\" & _
"SoftwareAcademyContacts.mdb;"
rst.Open "tblQuestionnaire", cnn, _
adOpenKeyset, adLockOptimistic

Providers are used with ADO, not DAO. DAO recordsets do not have an Open method. adOpenKeyset and adLockOptimistic are ADO constants.

Change it to

Dim cnn As DAO.Database

Set cnn = dbengine.Workspaces(0).OpenDatabase("C:\my.mdb&quot
Set rst = cnn.OpenRecordset("MyTable",dbOpenDynaset,,dbOptimisic)

replacing the db path and table names appropriately. Jon Hawkins

 

[Guest_imported]

As far as your error is concerned this seems to be a datatype mismatch where your data and code performs in any one of the following

1. Storing mismatch of data to the column in table.
2. Performing operations on two different data type.

Beyond this whatever may be your data access method the error seems to be the above.

Ananth-India

 

[cynaptic]

Jon

Thanks so much Jon, it is working fine, looks like its going through but then pops and error :

3001:invalid argument

Sorry to include whole code again but I am not sure which bit is causing the error.....

I feel so close to cracking this - it is almost painful!


*****

Option Compare Database
Option Explicit

Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rst As DAO.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean
Dim cnn As DAO.Database

On Error GoTo ErrorHandling

strDocName = "C:\My Documents\Questionnaire\" & _
InputBox("Enter the name of the Word Document " & _
"you want to import:", "Import Document&quot

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

Set cnn = DBEngine.Workspaces(0).OpenDatabase("c:\My Documents\Questionnaire\Questionnaire.mdb&quot


Set rst = cnn.OpenRecordset("tblQuestionnaire", dbOpenDynaset, , dbOptimistic)

With rst
.AddNew
!Name = doc.FormFields("Name&quot.Result
!Location = doc.FormFields("Location&quot.Result
!University = doc.FormFields("University&quot.Result
!Qualification = doc.FormFields("Qualification&quot.Result
!GraduationDate = doc.FormFields("GraduationDate&quot.Result
!Looking = doc.FormFields("Looking&quot.Result
!CurrentState = doc.FormFields("CurrentState&quot.Result
!CurrentStateExpand = doc.FormFields("CurrentStateExpand&quot.Result
!HearAbout = doc.FormFields("HearAbout&quot.Result
!HearAboutExpand = doc.FormFields("HearAboutExpand&quot.Result
!SAPurpose = doc.FormFields("SAPurpose&quot.Result
!ScottishOnly = doc.FormFields("ScottishOnly&quot.Result
!Connection = doc.FormFields("Connection&quot.Result
!ConnectionExpand = doc.FormFields("ConnectionExpand&quot.Result
!SADoneforme = doc.FormFields("SADoneforme&quot.Result
!RateService = doc.FormFields("RateService&quot.Result
!Methods = doc.FormFields("Methods&quot.Result
!MethodsExpand = doc.FormFields("MethodsExpand&quot.Result
!SuccesfulMethods = doc.FormFields("SuccesfulMethods&quot.Result
!SuccesfulMethodsExpand = doc.FormFields("SuccesfulMethodsExpand&quot.Result
!SubmittedCV = doc.FormFields("SubmittedCV&quot.Result
!Impression = doc.FormFields("Impression&quot.Result
!ImpressionExpand = doc.FormFields("ImpressionExpand&quot.Result
!Improvements = doc.FormFields("Improvements&quot.Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
MsgBox "Questionnaire Imported!"
Cleanup:
Set rst = Nothing
Set doc = Nothing
Set cnn = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application&quot
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

 

[jonscott8]

Set rst = cnn.OpenRecordset("tblQuestionnaire", dbOpenDynaset, 0, dbOptimistic)

My fault. Jon Hawkins

 

[cynaptic]

Jon

Thanks for that. Again seems to work and the pops an error -

3265.Item not found in this collection

is this a problem with the path or resolving the fields?

Sorry to take up so much of your time but am DETERMINED to get this to work.

Alex

 

[cynaptic]

Jon

I have tracked down the problem line. I am going thru the code in debug and it is one of the fields that is causing the error. The field is null, however I don't understand why it choked all of the preceding fields went through including some null fields. Will an error occur if the field is null? if so is there a way round this?

Alex

 

[jonscott8]

AFA the null question, it depends if the table field is configured to allow null values. If it isnt, it will produce an error. But that error would be something like "field X cannot contain a null value", not the error you mention.

My guess is you've fat-fingered one of the field names in your code. Since you've identified the line of code, make sure the field names in your code match exactly to the field names in you word doc & db table. Jon Hawkins

 

[cynaptic]

Jon

You are absolutely right, it was the dreaded fat finger syndrome. the whole thing works like a dream. Thankyou so much for all your help ith this. This was My First VBA Application, and I couldn't have done it without you. I hope to be able to return the favour to some poor struggling fool in the future.

Thanks again

Alex