hello
Can be a table linked from a sql server database engin through an access front end Object , truncated ?
hello
Can be a table linked from a sql server database engin through an access front end Object , truncated ?
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.
Yes it can via a pass-through query (look up querydef objects).
Cheers,
Is there a vba function to excute it ?
thanks
Is there a vba function to excute it ?
thanks
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.I mean to delete an linked dbo table ,( as the photo ) to set table index to zero
if you mean truncate have you considered using docmd.execute "passthroughqueryname"Is there a vba function to excute it ?
Here is the code I used (adapted for your table name):
Cheers,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
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 ↓↓
Probably you didn't declare the strConnect...
Create a pass through query
TRUNCATE schemaName.TableName;
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,