code for Deleting and Importing tables automatically

[Prachi via AccessMonster.com]

Hi everyone

I am using MS access for some application.I am a completely novice to VB
scripting.
For this application I need to incorporate automatic refresh of data(that is
import data form an external database)(that is to include file-get external
data- import in a button click).I did follow the points given in the help to
write a macro for it but it does not work.It says "installed ISAM not found".
Also I will ahve to first delete the existing tables before importing the new
tables.(in a button click).I have ODBC connection installed on my PC that
gets data from a SQL server

Please can u direct me in doin the same

 

[Guest]

Hi

Do you want import the whole table or just the data. To import the whole
table you can just use a transferdatabase macro - specify the table name in
the macro.

To import just the data use something like this

Private Sub ButtonName_Click()
Dim IMPfile As TableDef
For Each IMPfile In CurrentDb.TableDefs
If IMPfile.Name = "SomeTempTable" Then
CurrentDb.TableDefs.Delete IMPfile.Name
End If
Next
DoCmd.TransferDatabase acImport, "Path to external DB.mdb", acTable,
"ExternalTableName", "SomeTempTable"
DoCmd.SetWarnings False
DoCmd.OpenQuery "SomeAppendQuery"
DoCmd.SetWarnings True
CurrentDb.TableDefs.Delete "SomeTempTable"
End Sub


If you don't understand the above - post back and I (or someone else) will
break it down.

 

[Prachi via AccessMonster.com]

Hi

Thanks for the reply.I want to import all the tables from the database(For
example table1,table2 and so on).I used the DeleteObject macro for deleting
them at a button click locally(Please correct me if i am wrong here).After
doin this I want import the refresehed tables at a press of a button.

When i use transfer database macro it gives me the error'Installed ISAM not
found"
which i don not understand because the connection is ODBC connection,Please
can you help me here.
Also I wrote a Module for it as follows which compiles but gives a runtime
error 2507

Private Sub Command8_Click()
DoCmd.TransferDatabase acImport, "ODBC Database", "DSN ", acTable,
"individual3", "individual3new"

End Sub

Is this correct and if not what is the right way to do it.Also when I use
FILE->Get External Data->Import-> it asks me for type of database followed by
selecting the DSN name followed by entering password for the database.HOw do
I incorporate all of this.

Hi

Do you want import the whole table or just the data. To import the whole
table you can just use a transferdatabase macro - specify the table name in
the macro.

To import just the data use something like this

Private Sub ButtonName_Click()
Dim IMPfile As TableDef
For Each IMPfile In CurrentDb.TableDefs
If IMPfile.Name = "SomeTempTable" Then
CurrentDb.TableDefs.Delete IMPfile.Name
End If
Next
DoCmd.TransferDatabase acImport, "Path to external DB.mdb", acTable,
"ExternalTableName", "SomeTempTable"
DoCmd.SetWarnings False
DoCmd.OpenQuery "SomeAppendQuery"
DoCmd.SetWarnings True
CurrentDb.TableDefs.Delete "SomeTempTable"
End Sub

If you don't understand the above - post back and I (or someone else) will
break it down.

Hi everyone

[quoted text clipped - 9 lines]

Please can u direct me in doin the same