Unanswered: error '3146' when importing file using access as front end into SQL Server
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.
Private Sub cmdImport_Click()
If IsNull(txtCustFilePath) Then
MsgBox ("Required file path is missing.")
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
'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
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
MsgBox "Data transferred. You can proceed..."
MsgBox "File already exist. Please check the file"