ACCESS, VBA: Fetch data from sql server and paste it into local table.

[sebekkg]

I have access file that was currently using linked table, but I didn't want it to always check connection to linked table at start because some users don't have access to the server, but they need to use. That's why i wanted to create vba script that will connect to sql server, and fetch data to put into local table. And also this file (as application or still as a file will be on SharePoint for ease of access for users who don't have access to the DB.

I already have code that works but it iterates import row by row and since my sql query is returning >30000 rows it takes a lot of time.

I wanted to take different approach and this is how far i could go but it still gives me an error.
Any help would be appreciated

Option Compare Database

Sub ImportDataFromSQLServer()
    On Error GoTo ErrorHandler
    
    ' Set connection parameters
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    ' Set query timeout to 40 seconds (adjust as needed)
    conn.CommandTimeout = 40
    
    ' Connection string for SQL Server (replace placeholders with your actual values)
    conn.ConnectionString = "Provider=SQLOLEDB;Data Source=server05p;Initial Catalog=REG_MSCRM;Integrated Security=SSPI;"
    
    ' Open the connection
    conn.Open
    
    ' Create a local table in Access (replace with your actual table name)
    Dim localTableName As String
    localTableName = "Customer_data"
    
    ' Set up a recordset to store the retrieved data
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    
     ' Delete existing data from the local table
    DoCmd.SetWarnings False ' Disable warnings
    
    DoCmd.OpenQuery "1_RemoveLocalData", acViewNormal, acReadOnly
    
    DoCmd.SetWarnings True ' Enable warnings
    
    ' Check if TempImportTable exists, and if so, delete it
    If TableExists("TempImportTable") Then
    DoCmd.DeleteObject acTable, "TempImportTable"
    End If
    
    
    ' Create a temporary table with the same structure as the result set
     CurrentDb.Execute "SELECT TOP 0 * INTO [TempImportTable] FROM [Rep_customer] C", dbFailOnError

    
       
' Define your SQL query directly in VBA
    Dim strSQL As String
     strSQL = "SELECT * FROM Rep_customer AS C"
    
    ' Open the recordset with the SQL query
    rs.Open strSQL, conn
    
    ' Create an ADO recordset for the temporary table
    Dim tempTableRs As Object
    Set tempTableRs = CreateObject("ADODB.Recordset")
    tempTableRs.Open "TempImportTable", conn, adOpenKeyset, adLockOptimistic

   ' Copy the data from the original recordset to the temporary table
    tempTableRs.CopyFromRecordset rs
   
    
    
    
    ' Insert data from the temporary table into the local table
    DoCmd.RunSQL "INSERT INTO Customer_data SELECT * FROM TempImportTable"
    
    ' Close the recordset
    rs.Close
    tempTableRs.Close
    

    
    ' Optional: Compact and repair the database to free up space
    DBEngine.CompactDatabase CurrentDb.Name, CurrentDb.Name
    
    ' Display a message (optional)
    MsgBox "Data imported successfully!", vbInformation
    
     Exit Sub

ErrorHandler:
    MsgBox "Error: " & Err.Description, vbExclamation
End Sub

Function TableExists(tableName As String) As Boolean
    Dim tdf As DAO.TableDef
    For Each tdf In CurrentDb.TableDefs
        If tdf.Name = tableName Then
            TableExists = True
            Exit Function
        End If
    Next tdf
    TableExists = False
End Function

 

[Micron]

Only anyone who has permissions for that server will be able to append data in the local table, so if anyone can run the code you will raise an error if they don't have the required permissions. Perhaps you knew that already. Much simpler and probably a lot faster if you link to the sql table (I presume you used a suitable ODBC type of driver) and only allow anyone with permissions to append the server data to your local table using simple Access queries and no code. You can also set it up so that your table is updated during off hours by using Task Scheduler. Users should not be able to see the nav pane objects, so they wouldn't even be able to try to open the linked table.

If you intend to run Access on Sharepoint or One Drive you should read this and note the warning

Import, link, or move data to SharePoint - Microsoft Support

You can import data from a SharePoint list into Access or link to a SharePoint list, which results in a linked table.

support.microsoft.com

but it still gives me an error.

that is of no help to anyone who might try to help you. Always point out what line raises an error, the error number (if there is one) and the error message.

 

[sebekkg]

I simplified the sql queries just because of privacy of the fields.
Initially it works with nicely with linked table but the problem is that there will be users who don't have access to DB so I wouldn't like for file to trying to find access to sql db because of linked table.

Authorized use would refresh data and reupload it to sharepoint (if i am not mistaken it is not possible to run the sql connection while on Sharepoint). And then "regular" user will just use access file for simple search of the data but not being able to see rest of the data in local table.

 

[Micron]

the problem is that there will be users who don't have access to DB so I wouldn't like for file to trying to find access to sql db because of linked table.

That makes no sense. If they don't have access to the db, then they can't be accessing the linked table. Even if they have access to the db but no permissions on the linked table it doesn't matter because I said you'd still have the local table that you'd be appending the server records to. If you don't want them to have access to the local table either, then you simply don't provide any means for them to do that. I have done this many times where common users cannot access the server tables but they could access the local tables where I had already copied the server records. An added benefit is that I only brought over the fields we needed instead of all of them. In fact, it is also possible for any db user to use your credentials to access the server data as read only. If that is the permissions you have on the server (linked) tables, then they only have read permissions as well. I would not advise that if you have edit/delete permissions on the server tables.