Solved .adp -> .accdb migration (1 Viewer)

[SuperShin]

Hi everyone, I'm a long time Access developer and I'm migrating an .adp over to a linked table .accdb with a MSSQL backend.

No big deal on the actual import of modules/forms/reports and I linked all the tables (althought it names every table dbo_[name of table] which is kind of annoying but Ill write a function to rename all the links later, my biggest head scratch is in some of the old code that used to work in 2010 access on an .adp that no longer works on 365:

I have a form with a list box to do a lookup on the form on the listbox after update code I have:

   ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.clone

    rs.Find "[Station ID] = " & Str(Nz(Me![Combo16], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

this ends in object doesn't support this method or property.

Now what I've done to try and get it to work is change rs from object to dao.recordset -- no go then i get a type mismatch on set rs=Me.recordset.

trying this gets me member not found on rs.find

Dim rs As DAO.Recordset
    
    
    Set rs = Me.Recordset.Clone
    
    rs.Find "[Station ID] = " & Me.Combo16
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I've tried using adodb.recordset clones and had no luck either. What am I missing here? This is a common technique all throughout the large .adp file so whatever works ill do it, but this also always worked for a standard .accdb file with linked tables to another .accdb file also so I'm confused as to why it no longer works, now that im using odbc to link to SQL


Thanks!

David

 

[NauticalGent]

Hi David, just a thought but whenever I use this type of search method, I've always used rs.FindFirst - give it a shot, what do you have to lose?

I THINK the issue is the recordset type: rs.Find is designed for ADO objects and FindFirst is for DAO which is what you Dim'd it as. Again, this is just my understanding and I could be wrong...

 

[GPGeorge]

Spoiler: etc

Hi everyone, I'm a long time Access developer and I'm migrating an .adp over to a linked table .accdb with a MSSQL backend.

No big deal on the actual import of modules/forms/reports and I linked all the tables (althought it names every table dbo_[name of table] which is kind of annoying but Ill write a function to rename all the links later, my biggest head scratch is in some of the old code that used to work in 2010 access on an .adp that no longer works on 365:

I have a form with a list box to do a lookup on the form on the listbox after update code I have:

   ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.clone

    rs.Find "[Station ID] = " & Str(Nz(Me![Combo16], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

this ends in object doesn't support this method or property.

Now what I've done to try and get it to work is change rs from object to dao.recordset -- no go then i get a type mismatch on set rs=Me.recordset.

trying this gets me member not found on rs.find

Dim rs As DAO.Recordset
   
   
    Set rs = Me.Recordset.Clone
   
    rs.Find "[Station ID] = " & Me.Combo16
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I've tried using adodb.recordset clones and had no luck either. What am I missing here? This is a common technique all throughout the large .adp file so whatever works ill do it, but this also always worked for a standard .accdb file with linked tables to another .accdb file also so I'm confused as to why it no longer works, now that im using odbc to link to SQL

Thanks!

David

With regard to the naming result. This is standard behavior when linking Access to SQL Server tables. SQL Server has schemas and tables. A schema is--for the purpose of this discussion--a container for a group of tables. The default is dbo, but you can create any schema you want and group related tables in that schema. For example HR schema might contain tables for Employees and Employment History and similar things. A Production schema might contain tables for Products, Production History and so on.

What happens is that Access must honor that schema.table structure, but without schemas in Access. Therefore, it retains that identity by creating the schema_table name. It usually ends up dbo_YourTableName, but it could be HR_Employees, and so on.

With regard to your code. Does it compile in Access in the accdb? I assume you have added Option Explicit to ALL modules?

 

[SuperShin]

Hi David, just a thought but whenever I use this type of search method, I've always used rs.FindFirst - give it a shot, what do you have to lose?

I THINK the issue is the recordset type: rs.Find is designed for ADO objects and FindFirst is for DAO which is what you Dim'd it as. Again, this is just my understanding and I could be wrong...

Winner Winner Chicken Dinner. I suppose .adp projects use ADODB by default and by defining just recordset.clone I didn't realize I'm using DAO with a different method aka .findfirst! Thanks! Whats funny is once i started find/replacing this i found tons of examples of me using .findfirst as well as several of .find, both worked in the .adp world....RIP

As far as George's response, thanks I understand how the shemas work I just wish Access by default imported the table name in SQL vs importing dob.[table name] because all of my custom code which is considerable has adodb and that will work fine but ALL forms immediately break that have tblStations as their data source or any view becuase that table/view "doesn't exist" anymore...[eye roll] So the solution for me will just be to write a function to loop through the linked tables and rename them all and replace the dbo_ with a blank string.

I have old linked table code from way back before they decided to add that as a feature so I'm not worried about it, just seems silly that they would include the schema with the table as the default.

 

[NauticalGent]

Winner Winner Chicken Dinner. I suppose .adp projects use ADODB by default and by defining just recordset.clone I didn't realize I'm using DAO with a different method aka .findfirst! Thanks!

Projects were being phased out when I first started using Access which is a shame because they seem to have been a great idea and worked well.

 

[SuperShin]

I used .adp literally from 2002 until now. it was very convenient to modify and create SQL tables and views directly from Access, RIP, it did work well. The other thing i noticed RIP to was Currentproject.connection or Currentproject.execute. Looks like Ill need to write a replacement for the connection string that used to work in .adp but now the currentproject.connenction is not my ODBC connection :/


FYI Here is the dirty solution to get rid of all the schema names I used I thought i'd share it with the forum, its using the adox reference which is listed as 'Microsoft ADO Ext. 6.0 for DDL and Security' its quick and dirty and obviously could fail under some odd real world examples but with about 300 tables and 500 views there is no way im not writing a function for it.

Public Function RenameLinkedTables()

    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table
  
    ' Open the catalog.
    cat.ActiveConnection = CurrentProject.Connection
  
    'Cycle through all tables.
    For Each tbl In cat.Tables
    ' Check to make sure each table is a linked table.
        If tbl.Type = "Pass-Through" Then
            'Set Name remove dbo_ from beginning
            If Left(tbl.Name, 4) = "dbo_" Then 'dbo. sql schema
                tbl.Name = Mid(tbl.Name, 5, Len(tbl.Name) - 3)
            End If
        End If
    Next
End Function

 

[Pat Hartman]

Here's a sample database that includes the code to get rid of dao_ or other prefixes from linked table names as well as a couple other useful functions to clean up Jet/ACE tables.

BTW regarding your converted code. You probably want to convert this form and others to use a more client/server friendly method. ADP's handled SQL Server tables more like Jet/ACE tables. The .mdb and .accdb databases do not. That means that you never want to have forms or reports bound to naked tables or to queries without selection criteria. That method requires that Access download the table or the recordset created by a query to memory on the local PC and use the findfirst method to navigate through the data. the point of using ODBC and Linked tables is to get the server to do the heavy lifting. For most main forms, your objective should be to return a SINGLE record if at all possible. That means you need to provide search criteria ahead of time. For complex situations, I use a separate search form that builds a where clause. The code runs a count query to determine how many rows will be returned. If the number is one, the code opens the single record form to the selected record. Otherwise, if the number is not too large to display, the code opens a not updateable list type form. The user can then filter further and double click on each record to open the single record form.

For forms that have only one or two search options, the form is bound to a query that references the search field on the form:

Select ... From ... Where SomeField = Forms!myform!SomeField.

The form opens empty initially. The user enters a search argument and tabs out of the field or presses a button and the code requeries the form to run the search.

Continuing to use the method that you have will almost certainly make the application much slower than it was as an ADP.

 

[The_Doc_Man]

Another thing that is a side issue but might explain why you found what you found... When .ADP files were in vogue, the default record types were ADO, but when .ADP files were de-emphasized and eventually disavowed, .DAO became the default again. So the recordset types and the code you are finding come from a situation when ADO would be chosen in the absence of a qualifier. Which is exactly what would have happened for this code snippet from your 1st post.

    Dim rs As Object

    Set rs = Me.Recordset.clone

Therefore, it might not be such a surprise to see that code was used that was specific to ADO without it being explicitly qualified that way.

 

[SuperShin]

Here's a sample database that includes the code to get rid of dao_ or other prefixes from linked table names as well as a couple other useful functions to clean up Jet/ACE tables.

BTW regarding your converted code. You probably want to convert this form and others to use a more client/server friendly method. ADP's handled SQL Server tables more like Jet/ACE tables. The .mdb and .accdb databases do not. That means that you never want to have forms or reports bound to naked tables or to queries without selection criteria. That method requires that Access download the table or the recordset created by a query to memory on the local PC and use the findfirst method to navigate through the data. the point of using ODBC and Linked tables is to get the server to do the heavy lifting. For most main forms, your objective should be to return a SINGLE record if at all possible. That means you need to provide search criteria ahead of time. For complex situations, I use a separate search form that builds a where clause. The code runs a count query to determine how many rows will be returned. If the number is one, the code opens the single record form to the selected record. Otherwise, if the number is not too large to display, the code opens a not updateable list type form. The user can then filter further and double click on each record to open the single record form.

For forms that have only one or two search options, the form is bound to a query that references the search field on the form:

Select ... From ... Where SomeField = Forms!myform!SomeField.

The form opens empty initially. The user enters a search argument and tabs out of the field or presses a button and the code requeries the form to run the search.

Continuing to use the method that you have will almost certainly make the application much slower than it was as an ADP.

I dont have a ton of forms with full table recordsets but I do have probably 20 or so, and they are only smaller tables nothing huge , nothing over 8k rows probably. My frustration is even if you have everything based on views or flat out SQL statements the schema is annoying because it literally breaks all of that and everything in between -- I get it though .adp's have been dead for about 10 years now, and I'm just now migrating mine because in true engineer mindset if its not broke why are we messing with it.

I'm just doing prelim testing on it now, there are still a few quirks here and there (one form complaining about the CONVERT Function which I haven't had a chance to run down im sure its a DAO vs ADODB issue, but in general I agree with you however there are 20 years of biz logic, legacy, blood sweat and tears in this database solution it is what it is (which is good though it is pretty powerful 50 years worth of HR Data in an HR centered solution with document scanning of over 150 GB of docs in hundreds of thousands of files with full ACL control, AHA course and card generation, FMLA, OSHA Compliance, Paid/unpaid Leave, QA/QI, Forms Management, Intranet integration, with a fully custom distance learning platform built on PHP and Articulate with over 65k courses served to date including the ability to sell those classes to the general public with full certificate generation, CE Recert tracking for certified personnel, Sleep Time calcs (who even has ever heard of that law!?), Integration with 5 other commercially available cloud packages, and two on premise enterprise CAD and inventory tracking packages including accounting & user accounts sync, Scheduling, corporation/station info, medical testing, DEA Narc Box Maintenance for controlled substances, Name Badges with RFID Card encoding for access doors control, 300 tables, 500+ SQL views/functions/stored procs literally hundreds of reports. If I had it all to do over again with what I know now, I'd have done things differently on several of my designs for sure. I might not have even chosen Access. However I'm glad I did and the commercially available stuff that I write now in Access, as well as this fully custom solution, is more elegant.

Believe me many of my friends who are Java, Swift, and Ruby people make fun of me all the time, but there is literally nothing I've ever wanted to do nor my employers wanted to do that I couldn't accomplish with VBA, access and SQL (and LAMP with PDO drivers.)

With the tests Ive done with the main forms that only return 5-8k records or so, I can't tell much of a difference on my dev machine I do have some tables with closer to a half million rows but I would never dream of putting something like that bound to a dataset with no Where clause, hell if you even tried to open that table via .adp you were in for a world of hurt and best not even attempt it.

I've already come up against the 10k row issues long ago in list boxes and drop downs etc so thats all mitigated these days to dynamically load those sort of things.

I'm just glad there are still great forums like this around and people still coding in VBA / Access long may it reign!

 

[NauticalGent]

I'm just glad there are still great forums like this around and people still coding in VBA / Access long may it reign!

 

[Pat Hartman]

I've been using databases linked to server side RDBMS since my very first Access application for Readers' Digest in the early 90's. I was very excited when MS announced the ADP and jumped right in. And stopped almost immediately because the ADP couldn't link to anything except SQL Server. So I couldn't convert my old apps because they used IBM's DB2. The ADP couldn't even link to Jet tables. What I was doing with plain ol' Access was more flexible so I stopped immediately and stuck with Access.

 

[GPGeorge]

Winner Winner Chicken Dinner. I suppose .adp projects use ADODB by default and by defining just recordset.clone I didn't realize I'm using DAO with a different method aka .findfirst! Thanks! Whats funny is once i started find/replacing this i found tons of examples of me using .findfirst as well as several of .find, both worked in the .adp world....RIP

As far as George's response, thanks I understand how the shemas work I just wish Access by default imported the table name in SQL vs importing dob.[table name] because all of my custom code which is considerable has adodb and that will work fine but ALL forms immediately break that have tblStations as their data source or any view becuase that table/view "doesn't exist" anymore...[eye roll] So the solution for me will just be to write a function to loop through the linked tables and rename them all and replace the dbo_ with a blank string.

I have old linked table code from way back before they decided to add that as a feature so I'm not worried about it, just seems silly that they would include the schema with the table as the default.

Not to engage in a debate over what amounts to a minor issue in this context, but that would be impractical in some situations. The fact of the matter is that two different schemas in a SQL Server database could contain tables of the same name: dbo.Products and Inventory.Products, for example. Dropping the schema would not work in such cases. The problem is, in part, that "one size doesn't fit all", here and in so many other situations.

Pat pointed out that a huge limitation in ADPs themselves was that "one database fits all", i.e. SQL Server, simply doesn't work.
BTW: Here's code to rename local Access tables by removing prefixes. It works for all of them, not just dbo, by taking the prefix as an argument:


'---------------------------------------------------------------------------------------
' Procedure : tableNameClean
' Author : George
' Date : 4/30/2009
' Purpose : Removes a specified string from the name of every table in the database
'---------------------------------------------------------------------------------------
'
Public Sub tableNameClean(byVal strdboLocal As String)

Dim tdef As DAO.TableDef

For Each tdef In CurrentDb.TableDefs
If InStr(1, tdef.Name, strdboLocal) > 0 Then tdef.Name = Replace(tdef.Name, strdboLocal, "")
Next tdef


End Sub

 

[Pat Hartman]

The code I posted also works for all prefixes.