Problems with Access Front End to SQL Server Tables

[dantebothermy]

I have an Access application for which I'm trying to put the data on SQL Server. The data got there fine, but I'm having a problem running the application.

The application is a membership enrollment application. The scripts behind some forms (buttons) crash when they attempt to add a new record to a table. The command line is "DoCmd.GoToRecord , , acNewRec"

Can someone suggest a different approach for adding a new record?

Does anyone have other advice about problems I'm likely to encounter?

Thanks,


Dante

[missinglinq]

Does the line

"DoCmd.GoToRecord , , acNewRec"

actually have the quotation marks around it in code, or did you just add that for emphasis here?

Also, define "crash." Did the db actually crash or is there an error message?

Linq ;0)>

[dbrother]

You might want to check to see if it's a permissions issues. Does the account you're logged in as have write access to the SQL server?

I've always found it easier to link the SQL table to your Access program, then make a connection to it using CurrentProject.Connection and modify it as if it were a 'local' table.

[dantebothermy]

Linq

1: The quotes are not in the code
2: The crash is an error message: "Run-Time Error 2105. You can't go to the specified record.

DBrother
Can you give me any insight about how to find the CurrentProject.Connection? It sounds like exactly what I'm looking for.


Dante

[dbrother]

Firstly, are you sure it's not a permissions issue? The error " you can't go to the specified record" seems like you can't create a new record, which means you don't have access.

You will have to do a good bit of research on ADODB recordsets and connections to do exactly what you want to do, but I will provide some coding of such an example. The process below goes as follows:

1) Just getting a formatted date string (no relevance)
2) I know that I will be using 2 different recordsets here, 1 to check a local temp data table and one with the SQL server data
3) strSQLSN is a variable of a SQL string that runs a query on the SQL table
4) I open the SQL (remote) recordset and run the query from strSQLSN
5) It checks the table for a duplicate, gives error if true, and resets.
6) Close and reopen the recordset and check to see if we have an active connection to the DB (network is active) if not, store data in temp table.
7) If so, check temp table for data and upload records into SQL table if temp records exist.
8) Close connections and empty recordsets when you are through with them.
9) Just updating a table (no relevance)

Good Luck. Do your research on VBA ADODB recordsets and connections. Also, test your code on a DEV or TEST server to make sure you're getting the desired results.

Here's the code:

Public Sub cmdPROCESS_DATA()
 
Dim strDoM As String
strDoM = Format(Date, "MM-DD-YY")
 
 
' Make connection to Remote DB
Set conRemote = CurrentProject.Connection
Set rstRemote = New ADODB.Recordset
 
' Make connection to Local DB
Set conLocal = CurrentProject.Connection
Set rstLocal = New ADODB.Recordset
 
 
strSQLSN = "SELECT dbo_AUB_RETROFIT_DATA.SERIAL_NUM"
   strSQLSN = strSQLSN & " FROM dbo_AUB_RETROFIT_DATA"
   strSQLSN = strSQLSN & " WHERE (((dbo_AUB_RETROFIT_DATA.SERIAL_NUM)=""" & strSN & """));"
 
rstRemote.Open "dbo_AUB_RETROFIT_DATA", conRemote, adOpenDynamic, adLockOptimistic
   Set rstRemote = conRemote.Execute(strSQLSN)
 
lblStatus.Caption = "VALIDATING SERIAL NUMBER..."
 
 
If Not rstRemote.EOF Then
   rstRemote.MoveFirst
End If
 
If Not rstRemote.EOF Then
          Me.lblStatus.Caption = "SERIAL NUMBER REJECTED"
          MsgBox "SERIAL NUMBER: " & strSN & " REJECTED" & vbCrLf & "NUMBER ALREADY IN USE", vbCritical, "SERIAL IN USE"
          cmdRESET
          Exit Sub
End If
 
rstRemote.Close
 
 
rstRemote.Open "dbo_AUB_RETROFIT_DATA", conRemote, adOpenDynamic, adLockOptimistic
rstLocal.Open "tblTemp_Data", conLocal, adOpenDynamic, adLockOptimistic
rstRemote.MoveFirst
rstRemote.MoveLast
 
 
If rstRemote.State = 0 Then
                  'MsgBox "NO CONNECTION", vbCritical, "DB CONNECT"
                  'End
 
              If rstLocal.State = 0 Then
                   MsgBox "PROGRAM ERROR: NO DATABASE CONNECTED." & vbCrLf & "Close program and restart", vbCritical, "ERROR"
                   cmdRESET
                  End
              End If
 
             With rstLocal
                  .AddNew
 
                   .Fields(1).Value = strKit
                   .Fields(2).Value = strMuffType
                   .Fields(3).Value = strMuffNum
                   .Fields(4).Value = strSN
                   .Fields(5).Value = strCarbName
                   .Fields(6).Value = strDoM
 
                  .Update
             End With
          Else
 
              'CHECK FOR CONNECTION TO LOCAL DB
               If rstLocal.State = 0 Then
                   MsgBox "PROGRAM ERROR: NO DATABASE." & vbCrLf & "Close program and restart", vbCritical, "ERROR"
                  cmdRESET
                  End
               Else
                  'Count records in "TEMP_DATA" to see if there are any records to upload
                   If Not rstLocal.EOF Then
                       rstLocal.MoveFirst
                       LDB_COUNT = 0
 
                       Do Until rstLocal.EOF
                           If rstLocal.EOF Then
                               Exit Do
                           End If
 
                           LDB_COUNT = LDB_COUNT + 1
 
                           If rstLocal.EOF Then
                               Exit Do
                           Else
                               rstLocal.MoveNext
                           End If
                       Loop
                   End If
 
 ' if LDB_COUNT is greater than 0 (zero) then we have data in temp table to upload.
               If LDB_COUNT > 0 Then
 
                      'MOVE TO FIRST RECORD IN TEMP TABLE
                   rstLocal.MoveFirst
 
                      'LOOP THROUGH DATA IN TEMP TABLE UNTIL ALL UOLOADED TO REMOTE DB
                   Do Until rstLocal.EOF
 
                       With rstRemote
                           .AddNew
 
                               .Fields(1).Value = strKit
                               .Fields(2).Value = strMuffType
                               .Fields(3).Value = strMuffNum
                               .Fields(4).Value = strSN
                               .Fields(5).Value = strCarbName
                               .Fields(6).Value = strDoM
 
                          .Update
                      End With
 
                      intLoopCNT = intLoopCNT + 1
                      rstLocal.Delete
                      rstLocal.Update
                          'RS_LOCAL_DB.MoveNext
                  Loop
                  MsgBox "UPLOADED: " & intLoopCNT & " to remote DB", vbInformation, "INFO"
              End If
 
                      'upload current data
                  With rstRemote
                      .AddNew
 
                               .Fields(1).Value = strKit
                               .Fields(2).Value = strMuffType
                               .Fields(3).Value = strMuffNum
                               .Fields(4).Value = strSN
                               .Fields(5).Value = strCarbName
                               .Fields(6).Value = strDoM
 
                      .Update
                  End With
 
 
                  'Debug.Print "DB IS OPEN " & intDB_STATE
          End If
      End If
      'CLOSE RST-------------------------------------------------
  rstRemote.Close
      'CLOSE RST-------------------------------------------------
 rstLocal.Close
 
      ' Update LAST SN table with new serial number
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblLast_SN SET [LAST_" & strSNPrefix & "_SN] = '" & strSN & "' WHERE [LAST_" & strSNPrefix & "_SN] = '" & strTempSN & "';")
 
End Sub
 

[dantebothermy]

Thanks, DBrother, but this is a case of lasers in the hands of cavemen. I'm still progressing from Neanderthal to Cro Magnon.

I'll try to decipher your (very generous) advice, but I'm afraid it will all be far over my head. (Also, I'm using Access 2003; from a quick search of the web, I don't think that CurrentProject.Connection is available for that version.)

thanks again,


Dante

[dbrother]

Yes CurrentProject.Connection is available for Access 2003. I use same method and version of Access, which is called ADO. ADO is Access's (newer versions) default data access method.

I realize you did a quick search and from your initial post, I could tell that you weren't very comfortable with the VBA syntax that is required to do what you want.

I also recommend the 'ACCESS 2003 VBA for dummies' (not saying that you are). This is a book that I picked up when I started a system's administrator position right out of college and it was very helpful and had a plethora of examples.

Some links for consideration:
http://www.vbexplorer.com/VBExplorer...er_ADO_DAO.asp

http://www.faculty.mcneese.edu/mpl/cs309/ADO/ado01.htm

http://support.microsoft.com/kb/184397

http://support.microsoft.com/kb/168336


Hope this helps.