Problems filling Combinationfield with ADO-method

M

Maenne

Hi everybody,

i hope you can help me.
Formerly i used a stored query to fill the rowsource of a
combinationfield.
Now i want to use the ADO-method to do the same, but
somthing is wrong with my access-program:

Sub OeffneDb(DbName As String)
Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DbName 'D:\ADOData.mdb"
End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM [Adressen]"
Me!Suche.RowSource = cn.Adressen '<-----that doesn't work
Call SchliesseDb

End Sub


The combinationfield is named by Me!Suche
My difficult is, that the query is not in mdb-file, where the form is placed. It is in an external mdb-file.

Can s.o. help me please? (sorry for my english, i'm german)
 
D

Douglas J. Steele

Try:

Set Me!Suche.RowSource = Adressen

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody,

i hope you can help me.
Formerly i used a stored query to fill the rowsource of a
combinationfield.
Now i want to use the ADO-method to do the same, but
somthing is wrong with my access-program:

Sub OeffneDb(DbName As String)
Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DbName
'D:\ADOData.mdb"
End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM [Adressen]"
Me!Suche.RowSource = cn.Adressen '<-----that doesn't
work
Call SchliesseDb

End Sub


The combinationfield is named by Me!Suche
My difficult is, that the query is not in mdb-file, where the form is
placed. It is in an external mdb-file.

Can s.o. help me please? (sorry for my english, i'm german)
 
M

Maenne

Hi,

thanks for your answer. I tried it out, but that produces
an error like (I try to translate)

runtime error '438'

Object does not support this property oder method
 
R

RoyVidar

Maenne said:
Hi,

thanks for your answer. I tried it out, but that produces
an error like (I try to translate)

runtime error '438'

Object does not support this property oder method

Douglas J. Steele said:
Try:

Set Me!Suche.RowSource = Adressen

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody,

i hope you can help me.
Formerly i used a stored query to fill the rowsource of a
combinationfield.
Now i want to use the ADO-method to do the same, but
somthing is wrong with my access-program:

Sub OeffneDb(DbName As String)
Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
DbName 'D:\ADOData.mdb"
End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM [Adressen]"
Me!Suche.RowSource = cn.Adressen '<-----that
doesn't work
Call SchliesseDb

End Sub


The combinationfield is named by Me!Suche
My difficult is, that the query is not in mdb-file, where the form
is placed. It is in an external mdb-file.

Can s.o. help me please? (sorry for my english, i'm german)
Click to expand...
Click to expand...

Try

Set Me!Suche.Recordset = Adressen

But - if I recall correct, you might have to set some of the recordset
properties, but I don't recall which - I think the following should
work though (but have added some extras):

Set Adressen = CreateObject("ADODB.Recordset")
With Adressen
.CursorType = 3 ' adOpenStatic
'.CursorLocation = 2 ' adUseServer
'.LockType = 3 ' adLockOptimistic
End With
 
M

Maenne

Sorry,
that didn't work too, but I told you something wrong first.

I used the following statement in my code:

Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM [Adressen];", cn, adOpenKeyset, adLockOptimistic, adCmdText

I thing that's what you meant.




RoyVidar said:
Maenne said:
Hi,

thanks for your answer. I tried it out, but that produces
an error like (I try to translate)

runtime error '438'

Object does not support this property oder method

Douglas J. Steele said:
Try:

Set Me!Suche.RowSource = Adressen

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody,

i hope you can help me.
Formerly i used a stored query to fill the rowsource of a
combinationfield.
Now i want to use the ADO-method to do the same, but
somthing is wrong with my access-program:

Sub OeffneDb(DbName As String)
Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
DbName 'D:\ADOData.mdb"
End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM [Adressen]"
Me!Suche.RowSource = cn.Adressen '<-----that
doesn't work
Call SchliesseDb

End Sub


The combinationfield is named by Me!Suche
My difficult is, that the query is not in mdb-file, where the form
is placed. It is in an external mdb-file.

Can s.o. help me please? (sorry for my english, i'm german)
Click to expand...
Click to expand...

Try

Set Me!Suche.Recordset = Adressen

But - if I recall correct, you might have to set some of the recordset
properties, but I don't recall which - I think the following should
work though (but have added some extras):

Set Adressen = CreateObject("ADODB.Recordset")
With Adressen
.CursorType = 3 ' adOpenStatic
'.CursorLocation = 2 ' adUseServer
'.LockType = 3 ' adLockOptimistic
End With
Click to expand...
 
R

RoyVidar

Maenne said:
Sorry,
that didn't work too, but I told you something wrong first.

I used the following statement in my code:

Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen];", cn, adOpenKeyset, adLockOptimistic, adCmdText

I thing that's what you meant.




RoyVidar said:
Maenne said:
Hi,

thanks for your answer. I tried it out, but that produces
an error like (I try to translate)

runtime error '438'

Object does not support this property oder method

Newsbeitrag Try:

Set Me!Suche.RowSource = Adressen

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody,

i hope you can help me.
Formerly i used a stored query to fill the rowsource of a
combinationfield.
Now i want to use the ADO-method to do the same, but
somthing is wrong with my access-program:

Sub OeffneDb(DbName As String)
Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
DbName 'D:\ADOData.mdb"
End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen]" Me!Suche.RowSource = cn.Adressen
'<-----that doesn't work
Call SchliesseDb

End Sub


The combinationfield is named by Me!Suche
My difficult is, that the query is not in mdb-file, where the form
is placed. It is in an external mdb-file.

Can s.o. help me please? (sorry for my english, i'm german)
Click to expand...

Try

Set Me!Suche.Recordset = Adressen

But - if I recall correct, you might have to set some of the
recordset properties, but I don't recall which - I think the
following should work though (but have added some extras):

Set Adressen = CreateObject("ADODB.Recordset")
With Adressen
.CursorType = 3 ' adOpenStatic
'.CursorLocation = 2 ' adUseServer
'.LockType = 3 ' adLockOptimistic
End With
Click to expand...
Click to expand...

Could we please have the whole code pertaining to this, and also
the last errormessage - when you assigned to the combos *recordset*
property (not rowsource).

Also, which version of Access and what is the row-source type of your
combo - I think it should be "table/query" for mdb file.

Is it adp or mdb?
 
M

Maenne

Hi,

The version is MsAccess 2000 with ending *.mdb
The error message in german:

Microsoft Visual Basic
Laufzeitfehler '428'
Objekt unterstützt diese Eigenschaft oder Methode nicht

and now the complete code:

'--------Module------------
Option Compare Database

Global cn 'Connection
Global Adressen 'Tabelle Adressen

Sub OeffneDb(DbName As String)

Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DbName

End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

'-----Form-------------------
Option Compare Database

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Set Adressen = CreateObject("ADODB.Recordset")

Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM [Adressen];", cn, adOpenKeyset, adLockOptimistic, adCmdText
Set Me!Suche.RowSource = Adressen
Call SchliesseDb

End Sub







RoyVidar said:
Maenne said:
Sorry,
that didn't work too, but I told you something wrong first.

I used the following statement in my code:

Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen];", cn, adOpenKeyset, adLockOptimistic, adCmdText

I thing that's what you meant.




RoyVidar said:
Maenne wrote:
Hi,

thanks for your answer. I tried it out, but that produces
an error like (I try to translate)

runtime error '438'

Object does not support this property oder method

Newsbeitrag Try:

Set Me!Suche.RowSource = Adressen

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody,

i hope you can help me.
Formerly i used a stored query to fill the rowsource of a
combinationfield.
Now i want to use the ADO-method to do the same, but
somthing is wrong with my access-program:

Sub OeffneDb(DbName As String)
Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
DbName 'D:\ADOData.mdb"
End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen]" Me!Suche.RowSource = cn.Adressen
'<-----that doesn't work
Call SchliesseDb

End Sub


The combinationfield is named by Me!Suche
My difficult is, that the query is not in mdb-file, where the form
is placed. It is in an external mdb-file.

Can s.o. help me please? (sorry for my english, i'm german)




Try

Set Me!Suche.Recordset = Adressen

But - if I recall correct, you might have to set some of the
recordset properties, but I don't recall which - I think the
following should work though (but have added some extras):

Set Adressen = CreateObject("ADODB.Recordset")
With Adressen
.CursorType = 3 ' adOpenStatic
'.CursorLocation = 2 ' adUseServer
'.LockType = 3 ' adLockOptimistic
End With
Click to expand...
Click to expand...

Could we please have the whole code pertaining to this, and also
the last errormessage - when you assigned to the combos *recordset*
property (not rowsource).

Also, which version of Access and what is the row-source type of your
combo - I think it should be "table/query" for mdb file.

Is it adp or mdb?
Click to expand...
 
R

RoyVidar

Maenne said:
Hi,

The version is MsAccess 2000 with ending *.mdb
The error message in german:

Microsoft Visual Basic
Laufzeitfehler '428'
Objekt unterstützt diese Eigenschaft oder Methode nicht

and now the complete code:

'--------Module------------
Option Compare Database

Global cn 'Connection
Global Adressen 'Tabelle Adressen

Sub OeffneDb(DbName As String)

Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
DbName

End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

'-----Form-------------------
Option Compare Database

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Set Adressen = CreateObject("ADODB.Recordset")

Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen];", cn, adOpenKeyset, adLockOptimistic, adCmdText Set
Me!Suche.RowSource = Adressen Call SchliesseDb

End Sub







RoyVidar said:
Maenne said:
Sorry,
that didn't work too, but I told you something wrong first.

I used the following statement in my code:

Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen];", cn, adOpenKeyset, adLockOptimistic, adCmdText

I thing that's what you meant.




Maenne wrote:
Hi,

thanks for your answer. I tried it out, but that produces
an error like (I try to translate)

runtime error '438'

Object does not support this property oder method

Newsbeitrag Try:

Set Me!Suche.RowSource = Adressen

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody,

i hope you can help me.
Formerly i used a stored query to fill the rowsource of a
combinationfield.
Now i want to use the ADO-method to do the same, but
somthing is wrong with my access-program:

Sub OeffneDb(DbName As String)
Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
DbName 'D:\ADOData.mdb"
End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen]" Me!Suche.RowSource = cn.Adressen
'<-----that doesn't work
Call SchliesseDb

End Sub


The combinationfield is named by Me!Suche
My difficult is, that the query is not in mdb-file, where the
form is placed. It is in an external mdb-file.

Can s.o. help me please? (sorry for my english, i'm german)




Try

Set Me!Suche.Recordset = Adressen

But - if I recall correct, you might have to set some of the
recordset properties, but I don't recall which - I think the
following should work though (but have added some extras):

Set Adressen = CreateObject("ADODB.Recordset")
With Adressen
.CursorType = 3 ' adOpenStatic
'.CursorLocation = 2 ' adUseServer
'.LockType = 3 ' adLockOptimistic
End With
Click to expand...

Could we please have the whole code pertaining to this, and also
the last errormessage - when you assigned to the combos *recordset*
property (not rowsource).

Also, which version of Access and what is the row-source type of
your combo - I think it should be "table/query" for mdb file.

Is it adp or mdb?
Click to expand...
Click to expand...

Haben Sie versücht was ich vorige mal sagte?

Set Me!Suche.Recordset = Adressen

nicht

Set Me!Suche.RowSource = Adressen

Also, Recordset nicht RowSource

___________________________________

Did you try what I said last time?

Set Me!Suche.Recordset = Adressen

not

Set Me!Suche.RowSource = Adressen

Again, Recordset not RowSource
 
M

Maenne

OK,

I understand I have to use Recordset instead of Rowsource.
It's right, that ist produced no errors, but that's something
different in the meaning
Now you can only see one column with the key [AdrId].
The rest of the columns [Nachname],[Vorname] I can't see.

AdrID
1
2
3
4
....

Or what's the difference between Recordsource and Rowsource?


RoyVidar said:
Maenne said:
Hi,

The version is MsAccess 2000 with ending *.mdb
The error message in german:

Microsoft Visual Basic
Laufzeitfehler '428'
Objekt unterstützt diese Eigenschaft oder Methode nicht

and now the complete code:

'--------Module------------
Option Compare Database

Global cn 'Connection
Global Adressen 'Tabelle Adressen

Sub OeffneDb(DbName As String)

Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
DbName

End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

'-----Form-------------------
Option Compare Database

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Set Adressen = CreateObject("ADODB.Recordset")

Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen];", cn, adOpenKeyset, adLockOptimistic, adCmdText Set
Me!Suche.RowSource = Adressen Call SchliesseDb

End Sub







RoyVidar said:
Maenne wrote:
Sorry,
that didn't work too, but I told you something wrong first.

I used the following statement in my code:

Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen];", cn, adOpenKeyset, adLockOptimistic, adCmdText

I thing that's what you meant.




Maenne wrote:
Hi,

thanks for your answer. I tried it out, but that produces
an error like (I try to translate)

runtime error '438'

Object does not support this property oder method

Newsbeitrag Try:

Set Me!Suche.RowSource = Adressen

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody,

i hope you can help me.
Formerly i used a stored query to fill the rowsource of a
combinationfield.
Now i want to use the ADO-method to do the same, but
somthing is wrong with my access-program:

Sub OeffneDb(DbName As String)
Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
DbName 'D:\ADOData.mdb"
End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen]" Me!Suche.RowSource = cn.Adressen
'<-----that doesn't work
Call SchliesseDb

End Sub


The combinationfield is named by Me!Suche
My difficult is, that the query is not in mdb-file, where the
form is placed. It is in an external mdb-file.

Can s.o. help me please? (sorry for my english, i'm german)




Try

Set Me!Suche.Recordset = Adressen

But - if I recall correct, you might have to set some of the
recordset properties, but I don't recall which - I think the
following should work though (but have added some extras):

Set Adressen = CreateObject("ADODB.Recordset")
With Adressen
.CursorType = 3 ' adOpenStatic
'.CursorLocation = 2 ' adUseServer
'.LockType = 3 ' adLockOptimistic
End With

--
Roy-Vidar


Could we please have the whole code pertaining to this, and also
the last errormessage - when you assigned to the combos *recordset*
property (not rowsource).

Also, which version of Access and what is the row-source type of
your combo - I think it should be "table/query" for mdb file.

Is it adp or mdb?
Click to expand...
Click to expand...

Haben Sie versücht was ich vorige mal sagte?

Set Me!Suche.Recordset = Adressen

nicht

Set Me!Suche.RowSource = Adressen

Also, Recordset nicht RowSource

___________________________________

Did you try what I said last time?

Set Me!Suche.Recordset = Adressen

not

Set Me!Suche.RowSource = Adressen

Again, Recordset not RowSource
Click to expand...
 
R

RoyVidar

Maenne said:
Hi,

The version is MsAccess 2000 with ending *.mdb
The error message in german:

Microsoft Visual Basic
Laufzeitfehler '428'
Objekt unterstützt diese Eigenschaft oder Methode nicht

and now the complete code:

'--------Module------------
Option Compare Database

Global cn 'Connection
Global Adressen 'Tabelle Adressen

Sub OeffneDb(DbName As String)

Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
DbName

End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

'-----Form-------------------
Option Compare Database

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Set Adressen = CreateObject("ADODB.Recordset")

Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen];", cn, adOpenKeyset, adLockOptimistic, adCmdText Set
Me!Suche.RowSource = Adressen Call SchliesseDb

End Sub







RoyVidar said:
Maenne said:
Sorry,
that didn't work too, but I told you something wrong first.

I used the following statement in my code:

Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen];", cn, adOpenKeyset, adLockOptimistic, adCmdText

I thing that's what you meant.




Maenne wrote:
Hi,

thanks for your answer. I tried it out, but that produces
an error like (I try to translate)

runtime error '438'

Object does not support this property oder method

Newsbeitrag Try:

Set Me!Suche.RowSource = Adressen

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody,

i hope you can help me.
Formerly i used a stored query to fill the rowsource of a
combinationfield.
Now i want to use the ADO-method to do the same, but
somthing is wrong with my access-program:

Sub OeffneDb(DbName As String)
Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
DbName 'D:\ADOData.mdb"
End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen]" Me!Suche.RowSource = cn.Adressen
'<-----that doesn't work
Call SchliesseDb

End Sub


The combinationfield is named by Me!Suche
My difficult is, that the query is not in mdb-file, where the
form is placed. It is in an external mdb-file.

Can s.o. help me please? (sorry for my english, i'm german)




Try

Set Me!Suche.Recordset = Adressen

But - if I recall correct, you might have to set some of the
recordset properties, but I don't recall which - I think the
following should work though (but have added some extras):

Set Adressen = CreateObject("ADODB.Recordset")
With Adressen
.CursorType = 3 ' adOpenStatic
'.CursorLocation = 2 ' adUseServer
'.LockType = 3 ' adLockOptimistic
End With
Click to expand...

Could we please have the whole code pertaining to this, and also
the last errormessage - when you assigned to the combos *recordset*
property (not rowsource).

Also, which version of Access and what is the row-source type of
your combo - I think it should be "table/query" for mdb file.

Is it adp or mdb?
Click to expand...
Click to expand...

Oh, sorry, now I see you're using Access 2000. The recordset property
that makes it possible to assign recordsets, became available in the
2002 version.

In the 2000 version, there are several limitations.

You can use Value list as RowSourceType and assign the result of the
recordset through the .GetString method - BUT - the limitation is
that the max length of the Value List is 2048 characters.

Me!Suche.RowSourceType = "Value List"
Me!Suche.RowSource = Adressen.GetString(adClipString,,";",";")

But again, the length of the Row Source cannot exceed 2048.

Another option, would be to use something like the following as
rowsource:

Me!Suche.RowSourceType = "Table/Query"
Me!Suche.RowSource = "SELECT [AdrId],[Nachname],[Vorname] FROM [" & _
DBNAME & "].[Adressen]"

I e, fetching it from the other database like the above, where the
resulting SQL could look like this

SELECT [AdrId],[Nachname],[Vorname] FROM [D:\ADOData.mdb].[Adressen]
 
M

Maenne

Hi Roy,

well done, that works very good, but does it work also
with other Db's like for xample MS-SQL-Server or Oracle ...?

Thanks a lot

RoyVidar said:
Maenne said:
Hi,

The version is MsAccess 2000 with ending *.mdb
The error message in german:

Microsoft Visual Basic
Laufzeitfehler '428'
Objekt unterstützt diese Eigenschaft oder Methode nicht

and now the complete code:

'--------Module------------
Option Compare Database

Global cn 'Connection
Global Adressen 'Tabelle Adressen

Sub OeffneDb(DbName As String)

Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
DbName

End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

'-----Form-------------------
Option Compare Database

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Set Adressen = CreateObject("ADODB.Recordset")

Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen];", cn, adOpenKeyset, adLockOptimistic, adCmdText Set
Me!Suche.RowSource = Adressen Call SchliesseDb

End Sub







RoyVidar said:
Maenne wrote:
Sorry,
that didn't work too, but I told you something wrong first.

I used the following statement in my code:

Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen];", cn, adOpenKeyset, adLockOptimistic, adCmdText

I thing that's what you meant.




Maenne wrote:
Hi,

thanks for your answer. I tried it out, but that produces
an error like (I try to translate)

runtime error '438'

Object does not support this property oder method

Newsbeitrag Try:

Set Me!Suche.RowSource = Adressen

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody,

i hope you can help me.
Formerly i used a stored query to fill the rowsource of a
combinationfield.
Now i want to use the ADO-method to do the same, but
somthing is wrong with my access-program:

Sub OeffneDb(DbName As String)
Set cn = CreateObject("ADODB.Connection")
Set Adressen = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
DbName 'D:\ADOData.mdb"
End Sub

Sub SchliesseDb()
Adressen.Close
cn.Close
End Sub

Private Sub Form_Open(Cancel As Integer)

Call OeffneDb("D:\MSAccess_ADO\ADOData.mdb")
Adressen.Open "SELECT [AdrId],[Nachname],[Vorname] FROM
[Adressen]" Me!Suche.RowSource = cn.Adressen
'<-----that doesn't work
Call SchliesseDb

End Sub


The combinationfield is named by Me!Suche
My difficult is, that the query is not in mdb-file, where the
form is placed. It is in an external mdb-file.

Can s.o. help me please? (sorry for my english, i'm german)




Try

Set Me!Suche.Recordset = Adressen

But - if I recall correct, you might have to set some of the
recordset properties, but I don't recall which - I think the
following should work though (but have added some extras):

Set Adressen = CreateObject("ADODB.Recordset")
With Adressen
.CursorType = 3 ' adOpenStatic
'.CursorLocation = 2 ' adUseServer
'.LockType = 3 ' adLockOptimistic
End With

--
Roy-Vidar


Could we please have the whole code pertaining to this, and also
the last errormessage - when you assigned to the combos *recordset*
property (not rowsource).

Also, which version of Access and what is the row-source type of
your combo - I think it should be "table/query" for mdb file.

Is it adp or mdb?
Click to expand...
Click to expand...

Oh, sorry, now I see you're using Access 2000. The recordset property
that makes it possible to assign recordsets, became available in the
2002 version.

In the 2000 version, there are several limitations.

You can use Value list as RowSourceType and assign the result of the
recordset through the .GetString method - BUT - the limitation is
that the max length of the Value List is 2048 characters.

Me!Suche.RowSourceType = "Value List"
Me!Suche.RowSource = Adressen.GetString(adClipString,,";",";")

But again, the length of the Row Source cannot exceed 2048.

Another option, would be to use something like the following as
rowsource:

Me!Suche.RowSourceType = "Table/Query"
Me!Suche.RowSource = "SELECT [AdrId],[Nachname],[Vorname] FROM [" & _
DBNAME & "].[Adressen]"

I e, fetching it from the other database like the above, where the
resulting SQL could look like this

SELECT [AdrId],[Nachname],[Vorname] FROM [D:\ADOData.mdb].[Adressen]
Click to expand...
 
R

RoyVidar

Maenne said:
Hi Roy,

well done, that works very good, but does it work also
with other Db's like for xample MS-SQL-Server or Oracle ...?

Thanks a lot
Oh, sorry, now I see you're using Access 2000. The recordset
property that makes it possible to assign recordsets, became
available in the 2002 version.

In the 2000 version, there are several limitations.

You can use Value list as RowSourceType and assign the result of the
recordset through the .GetString method - BUT - the limitation is
that the max length of the Value List is 2048 characters.

Me!Suche.RowSourceType = "Value List"
Me!Suche.RowSource = Adressen.GetString(adClipString,,";",";")

But again, the length of the Row Source cannot exceed 2048.

Another option, would be to use something like the following as
rowsource:

Me!Suche.RowSourceType = "Table/Query"
Me!Suche.RowSource = "SELECT [AdrId],[Nachname],[Vorname] FROM [" &
_ DBNAME & "].[Adressen]"

I e, fetching it from the other database like the above, where the
resulting SQL could look like this

SELECT [AdrId],[Nachname],[Vorname] FROM [D:\ADOData.mdb].[Adressen]
Click to expand...
Click to expand...

The Value List sample (GetString) should work regardless of where
the data comes from.

I don't think the other method work with other than Jet (mdb, mde
accdb, accde)

But, if you could link the tables, you could create queries directly
on them, and circumvent the value list length limitation.
 
M

Maenne

Thanks a lot Roy,
that was very helpfully

By

RoyVidar said:
Maenne said:
Hi Roy,

well done, that works very good, but does it work also
with other Db's like for xample MS-SQL-Server or Oracle ...?

Thanks a lot
Oh, sorry, now I see you're using Access 2000. The recordset
property that makes it possible to assign recordsets, became
available in the 2002 version.

In the 2000 version, there are several limitations.

You can use Value list as RowSourceType and assign the result of the
recordset through the .GetString method - BUT - the limitation is
that the max length of the Value List is 2048 characters.

Me!Suche.RowSourceType = "Value List"
Me!Suche.RowSource = Adressen.GetString(adClipString,,";",";")

But again, the length of the Row Source cannot exceed 2048.

Another option, would be to use something like the following as
rowsource:

Me!Suche.RowSourceType = "Table/Query"
Me!Suche.RowSource = "SELECT [AdrId],[Nachname],[Vorname] FROM [" &
_ DBNAME & "].[Adressen]"

I e, fetching it from the other database like the above, where the
resulting SQL could look like this

SELECT [AdrId],[Nachname],[Vorname] FROM [D:\ADOData.mdb].[Adressen]
Click to expand...
Click to expand...

The Value List sample (GetString) should work regardless of where
the data comes from.

I don't think the other method work with other than Jet (mdb, mde
accdb, accde)

But, if you could link the tables, you could create queries directly
on them, and circumvent the value list length limitation.
Click to expand...
 

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

Problem with ADO with Parameter Object 1
problem with importing data from access to excel 0

Top