ADO -DAO problem?

G

Gerry

Private Sub cmdWord_Click()

On Error GoTo ErrorHandler

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim appword As Word.application
Dim strCompanyName As String
Dim strContactName As String
Dim strWholeAddress As String
Dim strJobTitle As String
Dim docs As Word.Documents
Dim doc As Word.Document
Dim strWordTemplate As String
Dim strDocsPath As String
Dim strTemplatePath As String
Dim prps As Object
Dim strShortDate As String
Dim strLongDate As String
Dim strTest As String
Dim strAddress As String
Dim strCountry As String
Dim strSaveName As String
Dim strTestFile As String
Dim intSaveNameFail As Boolean
Dim i As Integer
Dim strSaveNamePath As String
Dim mysql As String
Dim naam_cl As String
Dim rs1 As Database


mysql = "SELECT qry_cofi_final.public_banker, qry_cofi_final.['090-
nummer'], qry_cofi_final.[Oude structuur], qry_cofi_final.naam_cliënt,
qry_cofi_final.leningnr, qry_cofi_final.RSS,
qry_cofi_final.huidige_rv, qry_cofi_final.EVVD_lening,
qry_cofi_final.EVVD_structuur, qry_cofi_final.strategy_name,
qry_cofi_final.KO, qry_cofi_final.kredietmarge, qry_cofi_final.MTM,
qry_cofi_final.[Euribor 3M], qry_cofi_final.[Marge DF], qry_cofi_final.
[Term structure], qry_cofi_final.[RV COFI KO 5,50% inclusief KM en
MTM], qry_cofi_final.[RV COFI KO 6% inclusief KM en MTM],
qry_cofi_final.[RV COFI KO 6,5% inclusief KM en MTM], qry_cofi_final.
[Vaste rv], qry_cofi_final.[Vlottende rv (3m hz)], qry_cofi_final.
[Restlooptijd_lening CVMS] FROM qry_cofi_final;"

myRecordSet.Open mysql
mysql.Fields(1).Name

---

On this last line of code I have on "mysql" a compile error: invalid
qualifier

Wat goes wrong?
kind regards
Gerry
 
P

Paolo

Hi Gerry,
why did you put quotation marks around 090-nummer?
I think that if 090-nummer is a field of your query you have to write it
without the quotation marks i.e.
qry_cofi_final.[090-nummer]

HTH Paolo
 
G

Gerry

I adapted the recordset; but it didn' t help.

mysql = "SELECT qry_cofi_final.[public_banker], qry_cofi_final.[090-
nummer], qry_cofi_final.[Oude structuur], qry_cofi_final.
[naam_cliënt], qry_cofi_final.[leningnr], qry_cofi_final.[RSS],
qry_cofi_final.[huidige_rv], qry_cofi_final.[EVVD_lening],
qry_cofi_final.[EVVD_structuur], qry_cofi_final.[strategy_name],
qry_cofi_final.[KO], qry_cofi_final.[kredietmarge], qry_cofi_final.
[MTM], qry_cofi_final.[Euribor 3M], qry_cofi_final.[Marge DF],
qry_cofi_final.[Term structure], qry_cofi_final.[RV COFI KO 5,50%
inclusief KM en MTM], qry_cofi_final.[RV COFI KO 6% inclusief KM en
MTM], qry_cofi_final.[RV COFI KO 6,5% inclusief KM en MTM],
qry_cofi_final.[Vaste rv], qry_cofi_final.[Vlottende rv (3m hz)],
qry_cofi_final.[Restlooptijd_lening CVMS] FROM qry_cofi_final;"
 
P

Paolo

Hi Gerry,
I didn't notice before but the fields exist in the myrecordset collection
that you open.
mysql is just the sql string that populate the data collection. So that to
know the name of fields(1) of the collection you opened you have to do that
(I added the msgbox instruction so it'll pop on the screen)

msgbox myRecordSet.Fields(1).Name

HTH Paolo
 
G

Gerry

Paolo,
myRecordSet.Open mysql
myRecordSet.Fields(public_banker).Name
MsgBox (myRecordSet.Fields(0).Name)

I have again a compile error: invalid use of property on
Name in
myRecordSet.Fields(public_banker).Name

Gerry
 
P

Paolo

Gerry,
actually the name property return the name of the i field in the field
collection where i is the index of the field. I'm pretty sure that
public_banker IS the name of your field! So what's the purpose of extracting
a name that you already know?
BTW the error is raised because the index must be a number so in your
example MsgBox (myRecordSet.Fields(0).Name) will return public_banker 'cause
public_banker is the first field in your data collection and the index begin
with 0 so the first field is identified by the index 0, the second field by
the index 1 and so on.

Cheers Paolo
 
G

Gerry

Thanks Paolo!

Where I want to go to is this:

Based on a sql string I want to export some data into a word document
(a template) which I want to personalise.
I want to save it into a word.doc format, the name of the document wil
have the name of the client.

I have a compile error on naam_cl in
Set naam_cl = Nz(myRecordSet![naam_cliënt])

Can you help me?



mysql = "SELECT qry_cofi_final.[public_banker], qry_cofi_final.[090-
nummer], qry_cofi_final.[Oude structuur], qry_cofi_final.
[naam_cliënt], qry_cofi_final.[leningnr], qry_cofi_final.[RSS],
qry_cofi_final.[huidige_rv], qry_cofi_final.[EVVD_lening],
qry_cofi_final.[EVVD_structuur], qry_cofi_final.[strategy_name],
qry_cofi_final.[KO], qry_cofi_final.[kredietmarge], qry_cofi_final.
[MTM], qry_cofi_final.[Euribor 3M], qry_cofi_final.[Marge DF],
qry_cofi_final.[Term structure], qry_cofi_final.[RV COFI KO 5,50%
inclusief KM en MTM], qry_cofi_final.[RV COFI KO 6% inclusief KM en
MTM], qry_cofi_final.[RV COFI KO 6,5% inclusief KM en MTM],
qry_cofi_final.[Vaste rv], qry_cofi_final.[Vlottende rv (3m hz)],
qry_cofi_final.[Restlooptijd_lening CVMS] FROM qry_cofi_final;"

myRecordSet.Open mysql

Set naam_cl = Nz(myRecordSet![naam_cliënt])
If naam_cl = "" Then

MsgBox "Naam cliënt ontbreekt!"

GoTo ErrorHandlerExit
End If
strWordTemplate = "Ontwerpbesluit_vast-vlottend.dot"
strLongDate = Format(Date, "mmmm d, yyyy")
strShortDate = Format(Date, "m - d - yyyy")
strSaveName = "Ontwerpbesluit COFI_vast-vlottend " & naam_cl
strSaveName = strSaveName & " op datum van " & strShortDate _
& ".doc"
strDocsPath = "j:\geert\SOS Pooling juni - september 2009\Output\"
Debug.Print "Docs path: " & strDocsPath
strTemplatePath = "j:\geert\SOS Pooling juni - september 2009\"
Debug.Print "Template path: " & strTemplatePath
strWordTemplate = strTemplatePath & strWordTemplate
Debug.Print "Word template and path: " _
& strWordTemplate
 
P

Paolo

I assume that naam_cl is a string and not an object so you mustn't use the
set to assign a value to it, just do as follow

naam_cl = Nz(myRecordSet![naam_cliënt])

HTH Paolo
 
G

Gerry

error n° -2147217904; description: No value given for one or more
required parameters
 
P

Paolo

Well, it's strange, it seem that some parameter is missing. If the error is
raised on naam_cl = Nz(myRecordSet![naam_cliënt]) the only thing that comes
in my mind is that in the qry_cofi_final query some parameter is required,
but I think the error must be raised when you open the recorset. Try to check
that, otherwise I don't have other ideas.
 
T

Tony Toews [MVP]

Gerry said:
Dim rs1 As Database
Click to expand...

You may want the above as ADO.database if that's appropriate for
Database. Although a variable called rs1 implies RecordSet.
mysql.Fields(1).Name
Click to expand...

I would never, ever depend on the order of fields in a query. If
someone ever changes the query in the code without looking below this
could cause you a lot of problems. Instead use

mysql!FieldName where ! is the exclamation point.

Tony
 
G

Gerry

Hello

I changed my code but unfortunately without any further progress. The
query is correct, but now I based myself on the report which generates
it.

it blocks again on naam_cl = myRecordSet![naam_cliënt]
error: No value given for one of the required parameters.

Here is the code.

Private Sub cmdWord_Click_Click()

On Error GoTo ErrorHandler

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim appword As Word.application
Dim strCompanyName As String
Dim strContactName As String
Dim strWholeAddress As String
Dim strJobTitle As String
Dim docs As Word.Documents
Dim doc As Word.Document
Dim strWordTemplate As String
Dim strDocsPath As String
Dim strTemplatePath As String
Dim prps As Object
Dim strShortDate As String
Dim strLongDate As String
Dim strTest As String
Dim strAddress As String
Dim strCountry As String
Dim strSaveName As String
Dim strTestFile As String
Dim intSaveNameFail As Boolean
Dim i As Integer
Dim strSaveNamePath As String
Dim mysql As String
Dim naam_cl As String
Dim rs1 As Database
Dim Myreport As Report
Dim Recsource As String

DoCmd.OpenReport "RPT_OLD_COFI_deel1", acViewDesign
Recsource = Reports!RPT_OLD_COFI_deel1.RecordSource
DoCmd.Close acReport, "RPT_OLD_COFI_deel1", acSaveNo
myRecordSet.ActiveConnection = cnn1
mysql = "SELECT * FROM [" & Recsource & "]"
myRecordSet.Open mysql, , adOpenDynamic, adLockBatchOptimistic

naam_cl = myRecordSet![naam_cliënt]
If naam_cl = "" Then

MsgBox "Naam cliënt ontbreekt!"

GoTo ErrorHandlerExit
End If

End Sub
 
D

David W. Fenton

You may want the above as ADO.database if that's appropriate for
Database. Although a variable called rs1 implies RecordSet.
Click to expand...

I don't think ADO is the right method to use with MySQL, as so far
as I know, the only way to get to MySQL data from Access is via
MyODBC. In that case, ADO is not at all the best approach -- DAO
would be better.
 
G

Gerry

I don't think ADO is the right method to use with MySQL, as so far
as I know, the only way to get to MySQL data from Access is via
MyODBC. In that case, ADO is not at all the best approach -- DAO
would be better.
Click to expand...

Thanks for this usefull info. I started in DAO, I saw the progress.

Now I have this problem when generating my table in MS-word, I have a
table with 3 collumns (each of them containing 1 field). So after the
3th field is put into the 3th collumn, it should fill in the 1th field
of the following record in the 1the collumn on the second row, the
code here doesn't do this,
Can somebody look what has to be changed?

With rst
.MoveFirst
Do While Not .EOF
strLennr = Nz(![Leningnr])
Debug.Print "Product ID: " & strLennr
strRss1 = Format(Nz(![RSS]), "#,###.00")
Debug.Print "Product ID: " & strRss1
strEVVDlen = Nz(![EVVD_lening])
Debug.Print "Product ID: " & strEVVDlen


'Move through the table, writing values from the variables
'to cells in the Word table
With objWord.Selection
.TypeText Text:=strLennr
.MoveRight Unit:=wdCell
.TypeText Text:=strRss1
.MoveRight Unit:=wdCell
.TypeText Text:=strEVVDlen
End With
.MoveNext
Loop
.Close
End With
dbs.Close
 
D

David W. Fenton

:

I can't actually answer your question because I really don't
understand it, but I would definitely say that it's unwise to issue
a .Close for a recordset using a With block:
With rst
[...]
.Close
End With
Click to expand...

To me, that should be

With rst
[...]
End With
rst.Close

You really oughtn't be destroying the object with its own With
block.
 
G

Gerry

Ok thanks all of you guys! My last problem was solved.

..MoveRight Unit:=wdCell
..TypeText Text:=strEVVDlen
has to be followed by
..MoveRight Unit:=wdCell

greets
Gerry
 
Top