{UNSOLVED} Access Project Issues

[Nitro]

Hello All,

I have some questions about Access Projects. I recently converted an Access 2003 MDB to an 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 Back End servers.

I converted all the queries to SQL Views, and got the code to work. However there are still a few things 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.

[MMcCarthy]

Just adding a post to move it back up the list.

I'm afraid ADP's are not my area of expertise. We do have some experts on the matter though.

Hello All,

I have some questions about Access Projects. I recently converted an Access 2003 MDB to an 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 Back End servers.

I converted all the queries to SQL Views, and got the code to work. However there are still a few things 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.

[NeoPa]

Not mine either I'm afraid - Sorry.

[Nitro]

I'm thinking about going back to the MDB. The ADP seems to have many issues. I haven't yet started the reports, so I need to decide before I do.

Maybe someone can help me with another problem. This is the reason I changed to the ADP to begin with.

I was having problems getting my DB to Link to different SQL servers. I got it to work fine on my own machine, but when I distributed it at the client, and to another office, the Link didn't work. It either timed out, or gave errors.

I created a LinkTables Method (below) that would allow me to distribute the DB to the client running on another server. I created a local Settings table to store the Connection Strings of different locations. I wanted to either use a DSN file, or connect completely through code. I didn't want to setup System/User ODBC connections on anybody's machine. Unfortunately, I wasn't the one testing it out, but for some reason the tech could not get connected to SQL, without setup up a System ODBC manually. That's the reason I changed to ADP, because it was easier to connect.

Anyway, here's my code, and Test Connection String. Anyone have comments why this would not work? Also, what is required in the Connection String?

Thanks.

Public Sub LinkTables(id)
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, "\"))
 
    On Error Resume Next
    If id = "" Then Exit Sub
    c = DLookup("ConnStr", "Settings", "ID = '" & id & "'")
    If c = "" Then Exit Sub
    On Error GoTo Error
 
' Test Connection
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
 

[MMcCarthy]

To get proper attention to this issue I would start a new thread and copy and paste this question into it with a different title. It's more likely to be picked up by our experts.

I'm thinking about going back to the MDB. The ADP seems to have many issues. I haven't yet started the reports, so I need to decide before I do.

Maybe someone can help me with another problem. This is the reason I changed to the ADP to begin with.

I was having problems getting my DB to Link to different SQL servers. I got it to work fine on my own machine, but when I distributed it at the client, and to another office, the Link didn't work. It either timed out, or gave errors.

I created a LinkTables Method (below) that would allow me to distribute the DB to the client running on another server. I created a local Settings table to store the Connection Strings of different locations. I wanted to either use a DSN file, or connect completely through code. I didn't want to setup System/User ODBC connections on anybody's machine. Unfortunately, I wasn't the one testing it out, but for some reason the tech could not get connected to SQL, without setup up a System ODBC manually. That's the reason I changed to ADP, because it was easier to connect.

Anyway, here's my code, and Test Connection String. Anyone have comments why this would not work? Also, what is required in the Connection String?

Thanks.

Public Sub LinkTables(id)
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, "\"))
 
    On Error Resume Next
    If id = "" Then Exit Sub
    c = DLookup("ConnStr", "Settings", "ID = '" & id & "'")
    If c = "" Then Exit Sub
    On Error GoTo Error
 
' Test Connection
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
 

[PEB]

Once i've tried connection using ODBC to SQL Server... But i should create it manually in the ODBC settings....

When it was done there wasn't more problems with the connection...

But it was locally on my machine!

I'll try your code to see how it function on my machine... But this i'll do it next week on my work!