Delete data from Tables in a different access DB (1 Viewer)

[Jayce72]

I am trying to delete data from several tables in another DB - where the table name matches the tables in my current DB. I so far have:

Dim t As TableDef

DoCmd.SetWarnings False

For Each t In CurrentDb.TableDefs

If t.Name Like "*" Then
CurrentDb.Execute "Delete From T.name In 'C:\Users\InternetUser\Desktop\TestSD.accdb'"

End If

Next t

But alas it does not work. Also if I have a table thats not in my destination - just resume next I suppose

Any help please

 

[Ranman256]

attach the tables into your db, THEN run the delete code.
you can make a macro that deletes info from each tbl.
then just run the macro.

 

[Jayce72]

attach the tables into your db, THEN run the delete code.
you can make a macro that deletes info from each tbl.
then just run the macro.

I cant do that as the current DB is my master and the other is for training. Therefore, once a month I intend to delete all data from the training and then update it with new data. Hence I cant bring the Training tables into my live DB

But thank you

 

[jdraw]

Why not open the training database and delete all the tables, then import what is required from your master?

 

[Jayce72]

Why not open the training database and delete all the tables, then import what is required from your master?

I'm trying to automate this process from the live DB. Also, It's an access front end with Linked sql server tables. Therefore, cant import the tables as they would be the live ones.

Also, I do not have access to the sql server back end to do this there - therefore, got to try and automate this through access

 

[jdraw]

Ok, let's start again.
You have a SQL Server BE (Master) and an Access FE (the LIVE system)

The Training database is Access or SQL Server? Tell us more about the set up and what you need to happen --not how you intend to do it.
Who uses Training?

 

[Jayce72]

Ok, let's start again.
You have a SQL Server BE (Master) and an Access FE (the LIVE system)

The Training database is Access or SQL Server? Tell us more about the set up and what you need to happen --not how you intend to do it.
Who uses Training?

Sorry - was trying to keep it simple.

Right:

One live sql server with a front end access client
One training sql server with a front end access client

I do not have access to the sql server.

My Static data tables may change over time. Therefore, once a month I want to update all the Static data from live into training.

I currently have an automated process over night that runs MI and queries etc and I was going to incorporate it there if I could

 

[jdraw]

What is MI?
If you don't have access to SQL Server, how do you interact with your BE databases?
I'm not a sql server dba, but have worked with Oracle. With Oracle you could get a "data dump/unload" that could be used as input to another system. Perhaps there is a similar utility in SQL Server. Even a utility to export the tables to text or excel(csv) that could be run based on a schedule...

If your data doesn't have to be current (to the day or minute) perhaps you could get access to a read only copy of a recent sql server backup (or similar).

 

[Jayce72]

What is MI?
If you don't have access to SQL Server, how do you interact with your BE databases?
I'm not a sql server dba, but have worked with Oracle. With Oracle you could get a "data dump/unload" that could be used as input to another system. Perhaps there is a similar utility in SQL Server. Even a utility to export the tables to text or excel(csv) that could be run based on a schedule...

If your data doesn't have to be current (to the day or minute) perhaps you could get access to a read only copy of a recent sql server backup (or similar).

MI - Management Information

I have control of the data through access - but I dont have sql Server Management studio to manipulate the data at the server end.

 

[Jayce72]

I am trying to delete data from several tables in another DB - where the table name matches the tables in my current DB. I so far have:

Dim t As TableDef

DoCmd.SetWarnings False

For Each t In CurrentDb.TableDefs

If t.Name Like "*" Then
CurrentDb.Execute "Delete From T.name In 'C:\Users\InternetUser\Desktop\TestSD.accdb'"

End If

Next t

But alas it does not work. Also if I have a table thats not in my destination - just resume next I suppose

Any help please

Cracked it:


On Error GoTo err

Dim t As TableDef
Dim vs As String

DoCmd.SetWarnings False


For Each t In CurrentDb.TableDefs
If t.Name Like "*" Then

vs = t.Name

CurrentDb.Execute "Delete From " & vs & " In 'C:\Users\InternetUser\Desktop\TestSD.accdb'", dbFailOnError + dbSeeChanges

End If

Next t


err:
Resume Next

 

[moke123]

wouldn't system tables be part of the TableDef collection?

 

[Jayce72]

wouldn't system tables be part of the TableDef collection?

I intend to exclude those by re coding :

If t.Name Like "*" Then

to

If t.Name <> "Msys*" Then

Thanks

 

[moke123]

was just checking.
i use - If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then...
i believe "~*" excludes temp tables.