Results 1 to 13 of 13
  1. #1
    SHAMI is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2023
    Posts
    8

    Truncating a table linked from a sql server database engin through an access front end Object

    hello


    Can be a table linked from a sql server database engin through an access front end Object , truncated ?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What does truncating mean in this context? What exactly do you want to accomplish?

    Access can link to SQLServer tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Yes it can via a pass-through query (look up querydef objects).
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    SHAMI is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2023
    Posts
    8
    I mean to delete an linked dbo table ,( as the photo ) to set table index to zero .Click image for larger version. 

Name:	dboTb.jpg 
Views:	25 
Size:	17.0 KB 
ID:	49747
    after backup the table .

  5. #5
    SHAMI is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2023
    Posts
    8
    Is there a vba function to excute it ?
    thanks

  6. #6
    SHAMI is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2023
    Posts
    8
    Is there a vba function to excute it ?
    thanks

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I mean to delete an linked dbo table ,( as the photo ) to set table index to zero
    deleting a linked table simply removes the link, not the table it is linked to. Or do you mean delete the underlying table which is not the same as truncating? Or do you mean truncate (which removes all records from a table)? That will depopulate all the related table indexes.

    Is there a vba function to excute it ?
    if you mean truncate have you considered using docmd.execute "passthroughqueryname"

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is the code I used (adapted for your table name):
    Code:
    '<<<<<<<<<<<<to use it add these two lines to your procedure>>>>>>>>>>>>>>>>>>>>
    ' get the connection string to use.
    strConnect = GetConnString("dbo_ProfileTbr") 'or use any other table linked to the same SQL BE
    RunPassThroughQuery "TRUNCATE TABLE dbo.ProfileTbr;", strConnect
    '<<<<<<<<<<<<to use it add these two lines to your procedure>>>>>>>>>>>>>>>>>>>>
    
    
    Function GetConnString(sTable as string) As String
    Dim td As TableDef
    Dim db As Database
    Dim strConnect As String
    Set db = CurrentDb()
    
    
    ' Loop through tables def and get the names table and find out the connect string and use that for
             ' connecting to the data.
             For Each td In db.TableDefs
                If td.Name = sTable Then
                    strConnect = td.Connect
                End If
             Next
        GetConnString = strConnect
        
    End Function
    
    
    Function RunPassThroughQuery(sql_string As String, sConnect As String)
    Dim MyDb As Database, MyQ As QueryDef
    Set MyDb = CurrentDb()
    
    
    ' Create a temporary QueryDef object that is not saved.
    Set MyQ = MyDb.CreateQueryDef("")
    
    
    MyQ.Connect = sConnect
    ' Set ReturnsRecords to false in order to use the Execute method.
    MyQ.ReturnsRecords = False
    
    
    ' Set the SQL property and concatenate the variables.
    MyQ.SQL = sql_string
    
    
    'Debug.Print MyQ.SQL
    MyQ.Execute
    MyQ.Close
    MyDb.Close
    Set MyQ=Nothing
    Set MyDb=Nothing
    
    
    End Function
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    SHAMI is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2023
    Posts
    8
    An error occured when I used the code !!!Click image for larger version. 

Name:	Untitled-1.jpg 
Views:	17 
Size:	259.6 KB 
ID:	49770

  10. #10
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Post up the actual code you use (Use Code Tags Please - it's the # in the editor)
    We can't see typo's or test it from a picture.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Probably you didn't declare the strConnect...
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    madpiet is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    122
    Create a pass through query

    TRUNCATE schemaName.TableName;

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Isn't this what we've been talking about since post # 3 , I gave the OP an example in case there is a need to truncate more than one table.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-17-2020, 03:17 PM
  2. Replies: 8
    Last Post: 05-16-2018, 06:04 PM
  3. Replies: 1
    Last Post: 07-13-2017, 05:43 PM
  4. Replies: 1
    Last Post: 06-09-2016, 04:03 PM
  5. Truncating SQL Server table from Access
    By lbrady in forum Import/Export Data
    Replies: 3
    Last Post: 03-01-2013, 04:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums