Access hangs when updating SQL Database

[dwg23]

I have a Access program that pulls info from an old Novell server and populates tables in the program.
Then the program goes out to a SQL Server on our website removes the information from the tables and inserts the updated info from the Access tables.

The problem comes in when the program is updating the T_Player tabel, it just hangs and eventually the Access program stops responding. This program has been working fine for years and as I am not much of an Access person I have no idea where to start.
I did do a compact and repair on the Access data base. This did not help.

Any ideas?

Thanks.

 

[sparkbyte]

Look at the VBA code for the form and see if you can find the code it is running to do the update on the SQL Server. Once you have found the code you can post it so that we can try to help you out.

The code may have the script hardcoded into it or it may be calling a user stored procedure and passing parameters. If it is running a uSP the code for it would be helpful as well.

Should be something simular to this.

Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim exAppWs As Worksheet
Dim rng As Excel.Range


' Run the StoredProcedure for the UPS Priority Report on the SQL Server
Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = CurrentProject.Connection
    .ActiveConnection.ConnectionTimeout = 150
    .CommandTimeout = 100
    .CommandType = adCmdStoredProc
    .CommandText = "usp_CentOps_UPS_Priority_Report"
    .Parameters.Append .CreateParameter("@DateParam1", adDate, adParamInput, 8, strdate1)
    .Parameters.Append .CreateParameter("@DateParam2", adDate, adParamInput, 8, strdate2)
    .Parameters.Append .CreateParameter("@UPSpriorityList", adVarChar, adParamInput, 20, strUPSpriorityList)
    
'    .Execute
'    .Execute , , adAsyncExecute
End With

Set rst = cmd.Execute    ' now we have a recordset returned

Thanks

John Fuhrman

 

[lameid]

Can you open the linked table and see records ok?

If not...


Hit ctrl + g

paste this in the bottom pane (immediate)...

? currentdb.tabledefs("T_Player").connect

push <enter>

That will give you your connection string for the SQL Server...

Mine for SQL 2008 looks something like...

ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=[red]<Servername>[/red]\[red]<Instance>[/red];UID=[red]<SQL_USERNAME>[/red];PWD=[red]<Password>[/red];APP=Microsoft Office 2003;WSID=CNU0390YG7;DATABASE=[red]<Database>[/red];

The red items are what you need to create a connection... you will not have a username and password if your server is using trusted authentication. Also you may have just a server name and not a \Instance depending on your verison.

If you get an error, it is because your database is not set to use the old DAO object model... Go to tools References and check somehting that begins Microsoft DAO (pick the highest version number available). Try again... now not to cause problems go back in and uncheck it.

Now you should have all the information you need to link tge table again... you want an O D B C table (no spaces). I wrote it like that to keep this popping up in searches. You should be able to find something easily that summarizes how to do that with a search Engine.

My guess is if the table does not work now, it won't fix it but try it to be sure, and then find a DBA and ask him what the problem with connecting is.... Be sure to have the red text items if not the whole connect string.

Good luck!

 

[dwg23]

OK,
I think this is what you are looking for.
everything works until it gets to the T_Player Database.

Attribute VB_Name = "odbc"
Option Compare Database
Option Explicit

'------------------------------------------------------------
' test_odbc1
'
'------------------------------------------------------------
Function odbc()
'On Error Resume Next

Dim LocalDB As Database
Dim localpub As Connection

Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim conLoop As Connection
Dim prpLoop As Property
Dim start As Variant
Dim msg As String
Dim brian As Integer
Dim showsettings As String
Dim allowsettings As String
Dim executeFinalRatings As Boolean
Set LocalDB = CurrentDb()



'Forms!F_ProgressBox!TopTitle.Caption = "Connecting to Web"
'Forms!F_ProgressBox!BottomTitle.Caption = "Connecting to Web"
Forms!F_ProgressBox!FileLabel.Caption = "File:

http://www.medalistgames.com"

Forms!F_ProgressBox!ZoneLabel.Caption = "Zone: ALL"
Forms!F_ProgressBox!Statuslabel.Caption = "Status: Updating Server"
Forms!F_ProgressBox.Repaint

On Error GoTo test_odbc1_Err
start = Time()

' Create ODBCDirect Workspace object and open Connection

' objects.
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("Connection1", , , _
"ODBC;UID=update;PWD=update9;DSN=DartNet")

'Forms!F_ProgressBox!TopTitle.Caption = "Block Web Site"
'Forms!F_ProgressBox!BottomTitle.Caption = "Block Web Site"
' Forms!F_ProgressBox!Statuslabel.Caption = "Status: Connect"
Forms!F_ProgressBox!FileLabel.Caption = "All Database Areas"
Forms!F_ProgressBox.Repaint
'conPubs.Execute ("UPDATE T_DartSystem SET Updating = 1")

'Forms!F_ProgressBox!TopTitle.Caption = "Updating Web Site"
'Forms!F_ProgressBox!BottomTitle.Caption = "Updating Web Site"
' If we update the web, we always will update this DB
Forms!F_ProgressBox!FileLabel.Caption = "File: T_Dates"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("delete from T_Dates")
LocalDB.Execute ("INSERT INTO Web_Dates SELECT T_Dates.* FROM T_Dates")

'DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=DartNet;UID=none;PWD=test", acTable, "T_Dates", "T_Dates", False

' Check to see if we need to update the FLIGHTS DB
If Forms!F_ProgressBox!UpdateFlights.Value = True Then
Forms!F_ProgressBox!FileLabel.Caption = "File: T_Flights"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_Flights")
' DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=DartNet;UID=broal;PWD=test", acTable, "T_Flights", "T_Flights", False
LocalDB.Execute ("INSERT INTO Web_Flights SELECT T_Flights.* FROM T_Flights")
End If
' Check to see if we need to update the LOCATION DB
If Forms!F_ProgressBox!UpdateLocations.Value = True Then
Forms!F_ProgressBox!FileLabel.Caption = "File: T_Location"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_Location")
'DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=League;UID=sa;PWD=", acTable, "T_Location", "T_Location", False
LocalDB.Execute ("INSERT INTO Web_Location SELECT T_Location.* FROM T_Location")
End If
' Check to see if we need to update the MASTER SCHEDULES DB
If Forms!F_ProgressBox!UpdateMasters.Value = True Then
Forms!F_ProgressBox!FileLabel.Caption = "File: T_MasterSchedule4"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_MasterSchedule4")
' DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=League;UID=sa;PWD=", acTable, "T_MasterSchedule4", "T_MasterSchedule4", False
LocalDB.Execute ("INSERT INTO Web_MasterSchedule4 SELECT T_MasterSchedule4.* FROM T_MasterSchedule4")
Forms!F_ProgressBox!FileLabel.Caption = "File: T_MasterSchedule4_6"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_MasterSchedule4_6")
'DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=League;UID=sa;PWD=", acTable, "T_MasterSchedule4_6", "T_MasterSchedule4_6", False
LocalDB.Execute ("INSERT INTO Web_MasterSchedule4_6 SELECT T_MasterSchedule4_6.* FROM T_MasterSchedule4_6")
Forms!F_ProgressBox!FileLabel.Caption = "File: T_MasterSchedule6"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_MasterSchedule6")
'DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=League;UID=sa;PWD=", acTable, "T_MasterSchedule6", "T_MasterSchedule6", False
LocalDB.Execute ("INSERT INTO Web_MasterSchedule6 SELECT T_MasterSchedule6.* FROM T_MasterSchedule6")
End If
' Check to see if we need to update the OPERATOR DB
If Forms!F_ProgressBox!UpdateOperators.Value = True Then
Forms!F_ProgressBox!FileLabel.Caption = "File: T_Operator"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_Operator")
'DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=League;UID=sa;PWD=", acTable, "T_Operator", "T_Operator", False
LocalDB.Execute ("INSERT INTO Web_Operator SELECT T_Operator.* FROM T_Operator")
End If
' Check to see if we need to update the TEAM SCHEDULES DB
If Forms!F_ProgressBox!UpdateTeamSchedule.Value = True Then
Forms!F_ProgressBox!FileLabel.Caption = "File: T_TeamSchedules"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_TeamSchedules")
'DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=League;UID=sa;PWD=", acTable, "T_TeamSchedules", "T_TeamSchedules", False
LocalDB.Execute ("INSERT INTO Web_TeamSchedules SELECT T_TeamSchedules.* FROM T_TeamSchedules")
End If
' Check to see if we need to update the WEEKS DB
If Forms!F_ProgressBox!UpdateWeeks.Value = True Then
Forms!F_ProgressBox!FileLabel.Caption = "File: T_Weeks"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_Weeks")
'DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=League;UID=sa;PWD=", acTable, "T_Weeks", "T_Weeks", False
LocalDB.Execute ("INSERT INTO Web_Weeks SELECT T_Weeks.* FROM T_Weeks")
End If
' Check to see if we need to update the ZONE DB
If Forms!F_ProgressBox!UpdateZone.Value = True Then
Forms!F_ProgressBox!FileLabel.Caption = "File: T_Zone"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_Zone")
'DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=League;UID=sa;PWD=", acTable, "T_Zone", "T_Zone", False
LocalDB.Execute ("INSERT INTO Web_Zone SELECT T_Zone.* FROM T_Zone")
End If
' Check to see if we need to update the TEAM DB
If Forms!F_ProgressBox!UpdateTeam.Value = True Then
Forms!F_ProgressBox!FileLabel.Caption = "File: T_Team"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_Team")
'DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=League;UID=sa;PWD=", acTable, "T_Team", "T_Team", False
LocalDB.Execute ("INSERT INTO Web_Team SELECT T_Team.* FROM T_Team")
Forms!F_ProgressBox!FileLabel.Caption = "File: T_Forfeit"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_Forfeit")
'DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=League;UID=sa;PWD=", acTable, "T_Forfeit", "T_Forfeit", False
LocalDB.Execute ("INSERT INTO Web_forfeit SELECT T_Forfeit.* FROM T_Forfeit")
End If
' Check to see if we need to update the PLAYER DB
If Forms!F_ProgressBox!UpdatePlayer.Value = True Then
Forms!F_ProgressBox!FileLabel.Caption = "File: T_Player"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_Player")
'DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=League;UID=sa;PWD=", acTable, "T_Player", "T_Player", False
LocalDB.Execute ("INSERT INTO Web_Player SELECT T_Player.* FROM T_Player")
End If
' Check to see if we need to update the ROSTER DB
If Forms!F_ProgressBox!UpdateRoster.Value = True Then
Forms!F_ProgressBox!FileLabel.Caption = "File: T_Player_Team"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_Player_Team")
'DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=League;UID=sa;PWD=", acTable, "T_Player_Team", "T_Player_Team", False
LocalDB.Execute ("INSERT INTO Web_Player_Team SELECT T_Player_Team.* FROM T_Player_Team")
End If
' Check to see if we need to update the PLAYOFF SCHEDULE DB
If Forms!F_ProgressBox!UpdatePlayoff.Value = True Then
Forms!F_ProgressBox!FileLabel.Caption = "File: T_Playoff"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_Playoff")
'DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=League;UID=sa;PWD=", acTable, "T_Playoff", "T_Playoff", False
LocalDB.Execute ("INSERT INTO Web_Playoff SELECT T_Playoff.* FROM T_Playoff")
End If

Forms!F_ProgressBox!FileLabel.Caption = "File: T_Award"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_Award")
LocalDB.Execute ("INSERT INTO Web_Award SELECT T_Award.Zone, T_Award.PlayerID, T_Award.TeamID, T_Award.Award FROM T_Award")
conPubs.Execute ("update T_Award set decimalvalue=dbo.HEX2DEC(Award)")

Forms!F_ProgressBox!FileLabel.Caption = "File: T_Marks"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_Marks")
LocalDB.Execute ("INSERT INTO Web_Marks SELECT T_MARKS.PlayerID, T_MARKS.PlayerZone, T_MARKS.TeamID, T_MARKS.MARKS, T_MARKS.GAMES FROM T_MARKS")

Forms!F_ProgressBox!FileLabel.Caption = "File: T_Points"
Forms!F_ProgressBox.Repaint
conPubs.Execute ("Delete From T_Points")
LocalDB.Execute ("INSERT INTO Web_Points SELECT T_Points.PlayerID, T_Points.PlayerZone, T_Points.TeamID, T_Points.POINTS, T_Points.GAMES FROM T_Points")


'Forms!F_ProgressBox!TopTitle.Caption = "Disconnect From Web"
'Forms!F_ProgressBox!BottomTitle.Caption = "Disconnect From Web"
Forms!F_ProgressBox!FileLabel.Caption = "Setting System Values"
Forms!F_ProgressBox.Repaint
' Check Web Settings on Form
executeFinalRatings = False
showsettings = ""
allowsettings = ""
If Forms!F_ProgressBox!ShowTeamSchedules.Value = True Then
showsettings = showsettings & "/STS"
End If
If Forms!F_ProgressBox!ShowRollingRatings.Value = True Then
showsettings = showsettings & "/SRR"
End If
If Forms!F_ProgressBox!ShowFinalRatings.Value = True Then
showsettings = showsettings & "/SRF"
executeFinalRatings = True
End If
If Forms!F_ProgressBox!ShowPlayoffSchedule.Value = True Then
showsettings = showsettings & "/SPS"
End If
If Forms!F_ProgressBox!OperatorFinal.Value = True Then
showsettings = showsettings & "/SOF"
executeFinalRatings = True
End If

If executeFinalRatings = True Then
' conPubs.Execute ("exec spu_updateFinalRatingAndStatus")
End If

' Update Dart System with new settings and release the block

msg = "UPDATE T_System SET Updating = 0, ShowOptions='" & showsettings & "'"
conPubs.Execute (msg)

conPubs.Close
wrkODBC.Close

msg = "Started: " & start & vbCr & "End: " & Time()
'brian = MsgBox(msg, vbOKOnly)
test_odbc1_Exit:
Exit Function

test_odbc1_Err:
MsgBox Error$
Resume test_odbc1_Exit

End Function



Thanks for loking at this.