By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,260 Members | 1,677 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,260 IT Pros & Developers. It's quick & easy.

permanent delete in linked table

P: 232
i have a dbf file which i linked in access and running sql query through vba but delete query only marks for there any method to delete this record permanently as in foxpro after deletion we use pack to permanently delete a record.every time after running vba program i have to open foxpro use file and then pack to delete record permanently.
Apr 8 '15 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 5K+
P: 5,397
It appears that this is not directly possible from within MSAccess. You can mark the records for deletion... you can even set a flag in the local registry for MSAccess to ignore the marked records; however, there doesn't appear to be anyway to issue the PACK command from within VBA or ODBC.

I'll take another walk thru the internet to see if something pops up... would be helpful to know what OS and version of Office/Access/FoxPro you are using.

Option 1:
If you have the VfpOleDB.dll (MS link for v9)

Expand|Select|Wrap|Line Numbers
  1.    Dim cn As ADODB.Connection
  2.    Dim strPath as String
  3.    strPath = "your file's path"
  4.    Set cn = New ADODB.Connection
  5.    cn.Open ("Provider=vfpoledb.1;Data Source= strPath")
  6.    '
  7.    With cn
  8.       If .STATE = adStateOpen Then .Close
  9.       .Open
  10.       .Execute "PACK name_of_database.dbf"
  11.    End With
  12.    cn.Close
  13.    Set cn = Nothing
Option 2:
This is a VB workaround. Often VB can be modified to work within the VBA environment.
Unfortunately I do not have a FoxPro installation to check the functionality of the following: Support-Microsoft:How to ZAP or PACK a dBASE or FoxPro Table

The workaround requires an INI setting be made and then either uses a copy of at template file and the insert method or creates a temporary table, moves the good records, deletes the current table, renames the temporary table, finally refreshes the linked tables. I don't know how that will work in VBA and FoxPro.
Apr 18 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.