Link to home
Start Free TrialLog in
Avatar of Tusitala
Tusitala

asked on

Access 2010 - Issue Viewing Records Added to a Linked SQL Table

Hi all,

So, I am currently in the process of migrating an Access BE (2010) to SQL server (2014) and have hit an obstacle that I need some help with.

When adding around 2k records to a test table in SQLSVR, I can see the records appearing as expected inside SSMS, but when I try to view the added records in the Access FE, everything appears as #Deleted!

User generated image
The code below is what I am using to add the records from some local tables inside Access FE:

Sub AddTestRecords()
On Error GoTo Err_Handler
    
    'declare recordsets and connections ->
    Dim rs As New ADODB.Recordset
    Dim Conn As ADODB.Connection
    Set Conn = CurrentProject.AccessConnection
    Dim strSQL As String
    Dim dbFailOnError, dbSeeChanges
                          
    'declare variables ->
    Dim strUserName As String, dtmDateTime As String
    
    'set variables ->
    strUserName = "admin"
    dtmDateTime = Format(Now(), "yyyy-mm-dd hh:mm:ss AM/PM")
        
    strSQL = "MyTestSQL"
    Conn.Execute strSQL, dbFailOnError, dbSeeChanges
    
Err_Handler_Exit:
    Exit Sub

Err_Handler:
    If Err.Number = 0 Then
       Resume Err_Handler_Exit
    Else
       MsgBox ("Error " & Err.Number & " (" & Err.Description & ")"), vbOKOnly
          Resume Err_Handler_Exit
    End If
End Sub

Open in new window


The code below is what I use to link to the SQL database :

Sub LinkTable( _
    TableName As String, _
    TableNameSource As String, _
    ConnectNew As String, _
    TableDescription As String _
    )

   'DSN-Less connection to SQL Server
    On Error Resume Next
    
    'declare variables ->
    Dim dbCurrent As DAO.Database
    Dim prpCurrent As DAO.Property
    Dim tdfCurrent As DAO.TableDef
    
    'set database object ->
    Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
    dbCurrent.TableDefs.Delete TableName

    'create new TableDef object using DSN-less connection ->
    Set tdfCurrent = dbCurrent.CreateTableDef(TableName)
    tdfCurrent.SourceTableName = TableNameSource
    tdfCurrent.Connect = ConnectNew
    
    'append TableDef object ->
    dbCurrent.TableDefs.Append tdfCurrent
    
    'add the description properties to table ->
    If Len(TableDescription) > 0 Then
        Set prpCurrent = tdfCurrent.CreateProperty("Description", dbText, " ")
        tdfCurrent.Properties.Append prpCurrent
        tdfCurrent.Properties("Description") = TableDescription
    End If
    
    'cleanup ->
    Set tdfCurrent = Nothing
    Set dbCurrent = Nothing

End Sub

Open in new window


Sub CreateLinkSQL()
    LinkTable "test", "dbo.test", "ODBC;DRIVER={SQL Server};SERVER=myserver\test;DATABASE=test;Regional=Yes;Trusted_Connection=Yes;Connect Timeout=0", "(test table properties)"
    'LinkTable "test", "dbo.test", "ODBC;DRIVER={OBDC Driver 11 for SQL Server};SERVER=myserver\test;DATABASE=test;Regional=Yes;Trusted_Connection=Yes;Connect Timeout=0", "(test table properties)"
End Sub

Open in new window


I note some people are using "OBDC Driver 11 for SQL Server" but despite having the correct driver installed on my machine, it doesn't seem to work for me.  Keep getting the '3151' Error.

Can someone please put me on the right track?

TIA
~Tala~
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

I would recommend the "SQL Server Native Client 11.0" driver.

So, what code are you executing on the Server to add records to the server table?  You aren't writing to a temp table are you?

Dale
Avatar of Tusitala
Tusitala

ASKER

Hi Dale,

In my Access FE application that has linked tables to the BE that I am migrating to SQL, I'm using the below vanilla code (where my SQL is simply an insert statement) to add records to the test table in SQL Server:

Sub AddTestRecords()
On Error GoTo Err_Handler
    
    'declare recordsets and connections ->
    Dim rs As New ADODB.Recordset
    Dim Conn As ADODB.Connection
    Set Conn = CurrentProject.AccessConnection
    Dim strSQL As String
    Dim dbFailOnError, dbSeeChanges
                          
    'declare variables ->
    Dim strUserName As String, dtmDateTime As String
    
    'set variables ->
    strUserName = "admin"
    dtmDateTime = Format(Now(), "yyyy-mm-dd hh:mm:ss AM/PM")
        
    strSQL = "MyTestSQL"
    Conn.Execute strSQL, dbFailOnError, dbSeeChanges
    
Err_Handler_Exit:
    Exit Sub

Err_Handler:
    If Err.Number = 0 Then
       Resume Err_Handler_Exit
    Else
       MsgBox ("Error " & Err.Number & " (" & Err.Description & ")"), vbOKOnly
          Resume Err_Handler_Exit
    End If
End Sub

Open in new window


The table I am writing to in SQL server is a static table which I created for testing.  Nothing fancy at all.

I'm not sure if this means anything but I checked a few more things and found that I have three variants of the OBDC SQL Driver on my development machine which is running Access 2010 32-bit and SQL Server Enterprise 2014 64-bit.

~Tala~
SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The code:

strSQL = "MyTestSQL"
Conn.Execute strSQL, dbFailOnError, dbSeeChanges

Open in new window


Is what I am using to add test records to the SQL database.  It is called from the Access FE.

I already have the driver you mentioned so I shall give it a try and post back accordingly.

~Tala~
Hi Tala,

since the records are there, the statements you just posted appear to be executing ok ... just that it seems Access is not showing the changes.  What is open (tables, forms, etc) when this happens and what is not properly refreshing? Thanks
Hi Crystal,

Yes, all the code is executing fine, its just that I cannot see the resultant records in my linked table after executing.

What is open (tables, forms, etc) when this happens and what is not properly refreshing?

Nothing is open at all when I run the code. I am merely testing getting records into SQL and to see them in my Access FE after doing so. I was wondering of it is an issue with using SQL 64-bit with Access 32-bit as well but that doesn't seem like a valid reason to me. I am a noob with linking SQL Server to Access so most of this stuff is foreign to me at this point.

I am going to try your suggestions when I get back to that particular machine and will post back my results.

TFTH
~Tala~
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
its just that I cannot see the resultant records in my linked table after executing.
If you have a table open in DS view, you are NOT looking directly at the table.  You NEVER actually look at table data.  Access runs a query in the background to select all records from the table and that is what you are looking at.  Since the recordset is in memory, you would not expect to see new records.  You need to close the recordset and reopen it (on a form, you can do Me.Requery to rerun the query).

Also, if the rows are being added from within a transaction, Access may not fully "see" them until they are committed.
Ok, so after some further testing based on the suggestions provided by Dale and Crystal, my issue appears to have taken a turn for the worse!

So Crystal, that "Refresh All" command bar button you suggested, it kind of works in that when I click it, I can actually see ONE row of records! The other 1999 records are still showing as #Deleted!

Moving onward, I then tried using each of the three available drivers individually to create DSN-Less connections to my test table in SQL Server.  When running the connection routine, I am getting errors on two of the three drivers.   The only one that appears to be linking my table correctly is the old "SQL Server" driver.

However, now when the VBA code tries to execute (Conn.Execute) the insert SQL statement, it errors out with an OBDC error!

Following are the errors I am getting using each of the three OBDC drivers on my machine :

User generated imageUser generated imageUser generated image
Following are the references I have loaded in the Access FE  :

User generated image
Following are the drivers I have loaded on the machine :

User generated image
In response I tried compacting and decompiling the Access db as well with no improvement.  Could it be my using Access 32-bit with SQL Server 64-bit or not?  I also searched up the error codes and found a lot of conflicting information all over the place so I am really hoping that someone here can help me resolve the issue.

@ Pat

What do you mean?  I have used an Access 2003 FE linked to SQL before and had no issues viewing table data by double clicking on a table icon to view the underlying records in datasheet view.  Is this what you are referring to or have I misunderstood your question?  Also, I am not using an open recordset.  I am using an ADO Execute routine which works fine as I can see the 2k records in my SQL Server database.

~Tala~
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Jim!  BigInt was the problem!  I changed it and within seconds my data appeared in Access datasheet view!

Only thing left now is to sort out the driver problem as I can see some issues with the format of my dates (datetime vs datetime2) in Access and SQL.  I will post a new question for this shortly.

Thanks also to Dale, Pat and Crystal for your input.

~Tala~
Thanks Jim
Hi admin,

Please could you reopen this question so I can reassign points?

~Tala~
Hi all,

When reviewing this question yesterday I realized that I had not fairly assigned points for everyone that contributed.

Sorry Crystal and Dale :)

~Tala~