Convert from Access 2003 to Access 2010 3

[jpl458]

There are a number of problems, but I'll ask about one at a time.

I have a data entry application in Access 2003 that has problems in 2010. When i opn the Ap in 2010 the start form appears but hangs on the following piece of code I wrote:

'Set up select query
Dim cnndbo As ADODB.Connection
Set cnndbo = CurrentProject.Connection
Dim rsdbo As New ADODB.Recordset
rsdbo.ActiveConnection = cnndbo
rsdbo.CursorType = adOpenDynamic

I am using linked tables to SQL Server 2008

When I was first trying to figure out whose on first, I went to Tools/References in the VB editor looking for stuff that had a check mark. I tried un-checking everything that was checked (per a website), and one of those things had to do with ADO. When I found that the ap was hanging opening a linked table I went back Tools/References and the reference was gone.

When I go into design mode and bring up the editor I get the following message:
Compile error
User-defined type not defined

I would appreciate any help at all.

This probably the first of more questions regarding converting to 2010.

Thanks in advance

jpl

 

[MajP]

The error
User-defined type not defined

Comes from the fact that you are trying to define a variable type, that the system is not aware of. It gets its awareness to different objects by a reference to their object library. So for example if you removed the reference to ADO then when you define something as an
ADODB.
It has no idea what you are talking about, and assumes it is a user defined data type. So you have to have a reference to ADO.

References are a pain in upgrading because MS makes new object libraries and you have to ensure you have the most updated one.

Open the code and hit debug. Wherever you get this message, you need to set the reference.

 

[PHV]

You need to reference a Microsoft ActiveX Data Object Library.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

The Recordset object does not have an ActiveConnection property.

In the context of your code, you must OPEN a recordset, using the Connection object along with the other parameters required for opening a Recordset. Check ADO Help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I misspoke. The Recordset object DOES have an ActiveConnection property, but only AFTER you OPEN the recordset, which it does not appear that you have done in the context of your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpl458]

I am only slightly less confused. Scrounged the web, read a lot.

Here is a longer example of the code, which still works in
Access 2003, but not 2010.

'Add SQL to string for looking up Master_ID in
'Master Accounts Table on Server
Dim StrLUMID As Variant
StrLUMID = "SELECT dbo_Master_Accounts.Master_ID,"
StrLUMID = StrLUMID + " dbo_Master_Accounts.LastName,"
StrLUMID = StrLUMID + " dbo_Master_Accounts.FirstName,"
StrLUMID = StrLUMID + " dbo_Master_Accounts.Address_Line_1,"
StrLUMID = StrLUMID + " dbo_Master_Accounts.City,"
StrLUMID = StrLUMID + " dbo_Master_Accounts.State,"
StrLUMID = StrLUMID + " dbo_Master_Accounts.PostalCode,"
StrLUMID = StrLUMID + " dbo_Master_Accounts.Phone_Number_1,"
StrLUMID = StrLUMID + " dbo_Master_Accounts.Phone_Number_2,"
StrLUMID = StrLUMID + " dbo_Master_Accounts.Gender,"
StrLUMID = StrLUMID + " dbo_Master_accounts.Date_Of_Birth FROM"
StrLUMID = StrLUMID + " dbo_Master_Accounts"
StrLUMID = StrLUMID + " where dbo_Master_Accounts.Master_Id='" & Me.Card_No & "'"
'------------------------------------------------------------------------------
'Set up select query
Dim cnndbo As ADODB.Connection
Set cnndbo = CurrentProject.Connection
Dim rsdbo As New ADODB.Recordset
rsdbo.ActiveConnection = cnndbo
rsdbo.CursorType = adOpenDynamic
'----------------------------------------------------------------------------
'Send query to SQL Server
rsdbpen StrLUMID

The first problem is in the section 'Set up select query' as described in the previous note. But I can't figure out how to define the unknown variable, nor can I find what object library ADO is in. Adding to the confusion si the big changes in the UI. When I first looked in Referenced ADO had a check mark, which means there was a problem with it, according to an article.

I looked for a book on vba for 1020 and was not successful. I am looking for examples of how to define the variable in question.

Sorry if this sound confused and disjointed, but that is where I am, unfortunately.

If anyone can be of help, or point me to an book or article on converting from 2003 to 2010 I would be grateful.

Thanks jpl

 

[PHV]

nor can I find what object library ADO is in
Again: Microsoft ActiveX Data Object Library

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

Sorry for my lack of understanding, but where is the Microsoft ActiveX Data Object Library and how do I use it/incluse.reference in my code.

Thanks in advance

jpl. If there is a place that explains that I will read up on it

 

[jpl458]

Finally figured it out, I think, at least its running. My mistake was not examining all the references in the dropdown under Tools\references, and there they were - Activex, severeal of them. I just checked to 2 highest ones (rev) then ran the Ap again and lo it worked.

I had two problems - lack of experience, and switching from 2003 to 2010 is extreme from a visual standpoint, and it really takes some getting used to. But it seems like everythings in there, you just have to hunt for it.

Thanks for setting me on the right course. Appreciate it.

jpl