Hi

We have access serving as front end connecting to SQL server.
Our SQL Server was upgraded from version 2008 to 2008 r2 and the machine
has been replaced with a new one a couple of days ago as well. The access program
was working properly prior to the upgrade however we have been experiencing
one issue with it after the upgrade.

1.) We are getting below error when importing file into the SQL server
database using access, it is also calling a few stored procedure when
doing the file import. I have checked the ODBC profile and everything looks
ok. I have also tried to relinked the linked tables in access but it did not
fix the issue. I would appreciate if someone can help me on this.

Run-Time error '3146':
ODBC errors:
[Microsoft][SQL Server Native Client.10.0]Query expired
ODBC--call failed.

Access error:
ODBC--call failed.



Please see the Code below:

Private Sub cmdImport_Click()
If IsNull(txtCustFilePath) Then
MsgBox ("Required file path is missing.")
Exit Sub
End If




Dim strFileName As String
strFileName = GetextPart([txtCustFilePath])

'Insert data from Holding to Archieve table
'Delete data from Holding
Dim longEmpty As Long
longEmpty = DCount("TIME_PERIOD", "dbo_BS_NATIONAL_WKLY_ROLLING")


' to check against reference table if the file name existed
Dim ExistFile As String
ExistFile = Nz(DLookup("FILE_NAME", "File_Name", "FILE_NAME = '" & strFileName & "'"), "No File available")

Dim strSQL As String
strSQL = "EXEC INSERT_FILE_NAME_WK_ROL '" + strFileName + "'"


If ExistFile = "No File available" Then
Dim strRollingFilename As String


If longEmpty > 0 Then
'strRollingFilename = DMax("[FILE_NAME]", "[dbo_BS_NATIONAL_WKLY_ROLLING]")
'Dim Msg, Style, Title, Ctxt, Response, MyString
'Msg = "Do you want to archived previous week (Yes) or reupload the next file (No) :" + strRollingFilename + " ? "
'Style = vbYesNoCancel + vbQuestion + vbDefaultButton1
'Title = "Archived or Reupload"
'Ctxt = 1000

'Response = MsgBox(Msg, Style, Title, Help, Ctxt)

'Select Case Response
'Case vbYes: ' User chose Yes.
'Insert data from Weekly Rolling to Holding table
RunPassThruQuery "EXEC WKLYROLLINGARCHIVED", "ODBC;DSN=SQLServer_DEV;Description=SQLServer_DEV; Trusted_Connection=Yes;DATABASE=DEV;", False

'Case vbCancel:
'Exit Sub
'Case vbNo:
'MsgBox ("Second file's data will be uploaded") ' Perform some action.
'Delete data from Weekly Rolling - CAN NOT DELETE ANY MORE
'RunPassThruQuery "EXEC WKLYROLLINGDELETION", "ODBC;DSN=SQLServer_DEV;Description=SQLServer_DEV; Trusted_Connection=Yes;DATABASE=DEV;", False

'End Select

End If
MsgBox "Importing to table: Weekly Rolling View"

'to reset the primary key to 0
RunPassThruQuery "EXEC SP_RESET_IDENTITY", "ODBC;DSN=SQLServer_DEV;Description=SQLServer_DEV; Trusted_Connection=Yes;DATABASE=DEV;", False

'Imports data from folder to Weekly Rolling
DoCmd.TransferText acImportDelim, "ImportSpec", "dbo_BS_NATIONAL_WKLY_ROLLING", Me!txtCustFilePath, True


Dim strSQL1 As String
strSQL1 = "EXEC WKLYROLLINGUPDATEFILENAME '" + strFileName + "'"

'insert the filename and as_of_date
RunPassThruQuery strSQL1, "ODBC;DSN=SQLServer_DEV;Description=SQLServer_DEV; Trusted_Connection=Yes;DATABASE=DEV;", False

'delete any import bad data
DeleteImportErrorTbls

DoCmd.SetWarnings 0
MsgBox "Data transferred. You can proceed..."
Me.Requery


Else
MsgBox "File already exist. Please check the file"
End If
End Sub

Thank you in advance!