Access Project Issues

[Nitro]

Hello All,

I have some questions about Access Projects. I recently converted an Access 2003 MDB to ADP, because I was having difficulty linking the tables to SQL after moving it to different servers. I wanted an easier way to make the database portable. ie distribute the Front End to clients with different servers.

I converted all the queries to SQL Views, and got the code to work. However there are still a few thing that are not working as they should. I'll list them to make it clearer.

1. First off, how can I (is there a way to) distribute the ADE file to a client with a different SQL Server and Database Name, without displaying a Connection Error when they first open it? It looks for the old server. I know after they set the Connection, it works fine, but that error is annoying.

2. <FormName>.Recordset.Find doesn't seem to work anymore. When I try to Open a form and do a Find to place it on a Record, it doesn't work. Any idea why this is?

3. <FormName>.Recordset.AddNew acts weird. It doesn't add the record at the bottom, but instead, 2nd to last. Also, it doesn't fill in the default values as it should. Any idea's on this?

It seems there are bugs in the way Access forms deal with SQL tables in Projects. The funny thing is this all worked fine with the MDB, while Linking the tables to SQL using ODBC.

Any help would be much appreciated.

 

[Pat Hartman]

You are better off going back to the .mdb. Microsoft is no longer developing the .adp and will stop support at some time. They have had problems from the beginning and don't do many of the things that .mdb's can do.

If relinking is the only issue you were having, search the MS knowledgebase for articles on relinking tables. There is an example of doing it with code for Jet tables in the solutions.mdb and I think I've seen code solutions for ODBC tables in the kb. I don't have any code handy or I would post it.

1. No. How would Access know the name of the database or server if it was different?
2. Finds are pretty inefficient when you could be using queries with criteria that select only the records you want to process rather than the entire table. The recordset for a form in an .mdb is a DAO object. I don't know what it is in an .adp. It may be an ADO object and ADO may not support the Find method.
3. Part a - that's a mystery. Part b - are you sure that the defaults don't populate when the record is saved? Is this just an issue with what is showing on the form? With Jet tables, the defaults populate when a new record is started.

 

[Nitro]

You are better off going back to the .mdb. Microsoft is no longer developing the .adp and will stop support at some time. They have had problems from the beginning and don't do many of the things that .mdb's can do.

Really?!? That bites, considering I spent 4 days converting this thing over for free.

If relinking is the only issue you were having, search the MS knowledgebase for articles on relinking tables. There is an example of doing it with code for Jet tables in the solutions.mdb and I think I've seen code solutions for ODBC tables in the kb. I don't have any code handy or I would post it.

I have code to Relink tables. It worked fine linking to MDB's, and even SQL on my own machine, but I was having all kinds of issues linking to other servers. As a matter of fact, I had problems linking to the SQL server manually, so I just gave up.

Actually I was trying to use a DSN file and/or specifing the Connection String via code, but it was too inconsistant. What worked on my machine didn't work on others. I didn't want to have to setup a System/User ODBC on every users machine either.

1. No. How would Access know the name of the database or server if it was different?

Actually I created a Link Tables method that Connected to the Server via code. It works pretty slick if it weren't for the Error in the beginning.

2. Finds are pretty inefficient when you could be using queries with criteria that select only the records you want to process rather than the entire table.

I know, but we're only talking a few hundred records, 1000 max, so I didn't think it mattered.

The recordset for a form in an .mdb is a DAO object. I don't know what it is in an .adp. It may be an ADO object and ADO may not support the Find method.

ADO is supposed to support the Find method according to the Help. I think it's a bug in ADP.

3. Part a - that's a mystery. Part b - are you sure that the defaults don't populate when the record is saved? Is this just an issue with what is showing on the form? With Jet tables, the defaults populate when a new record is started.

They don't save at all. I think it's another bug.

I may go back to the MDB, if I can figure out how to Link the tables via code.

Here's my old code for linking tables. It worked fine on my machine, but it would just timeout, or give errors on other servers.

Public Sub LinkTables(id as String)
Dim DBPath As String
Dim tds As TableDefs
Dim td As TableDef
Dim x, y, p As Integer
Dim s, c As String

    On Error GoTo Error
    DBPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
    
' Get Connection string from Settings table based on ID
    On Error Resume Next
    If id = "" Then Exit Sub
    c = DLookup("ConnStr", "Settings", "ID = '" & id & "'")
    If c = "" Then Exit Sub
    On Error GoTo Error

' Force for testing
c = "ODBC;DRIVER=SQL Server;UID=user;PWD=pass;SERVER=CORPMAT;DATABASE=AuditTest"
    
    Set tds = CurrentDb.TableDefs
    y = tds.Count
    For x = 0 To y - 1
        Set td = tds(x)
        s = td.Connect
        If Left(s, 4) = "ODBC" Then ' Check for SQL Link
'        If Left(s, 9) = ";DATABASE" Then ' Check for MDB Link
            td.Connect = c
            td.RefreshLink
        End If
    Next
    MsgBox "Tables Linked Successfully."
leave:
    Set tds = Nothing
    Set td = Nothing
    Exit Sub
Error:
    MsgBox "Error Linking Tables! " + Error$, vbCritical
    Resume leave
End Sub