Link to home
Start Free TrialLog in
Avatar of rockas1982
rockas1982Flag for United States of America

asked on

copy database data from one database to onother same structure

i have 2 database same structure
i want to load one table(products) in visual basic.net and copy the data in the other database same table that is empty
Avatar of bprojoe
bprojoe

if you just want to move data from one database to another then create a dts job and call is from your vb code.
Avatar of rockas1982

ASKER

I don't know what dts is can u plz give me a sample code
i have a connection in visual basic that i change the connection string to select which database i want to work with
i'm thinking something like connecting to first database load data then connect to second database and paste the data that i ahve loaded
this is something i'm trying

            Ryth1.ΑΠΥ.Clear()
            DAapy.DeleteCommand.CommandText = "DELETE * FROM ΑΠΥ WHERE Κατάστημα='" & Katastima.Text & "'"
            DAapy.DeleteCommand.Connection = CON1
            DAapy.DeleteCommand.ExecuteNonQuery()
            DAapy.Update(Ryth1)

            Ryth1.ΑΠΥ.Clear()
            DAapy.SelectCommand.CommandText = "SELECT * FROM ΑΠΥ WHERE Κατάστημα='" & Katastima.Text & "'"
            DAapy.SelectCommand.Connection = CON2
            DAapy.Fill(Ryth1)
            Ryth1.ΑΠΥ.Copy()
            MsgBox(Ryth1.ΑΠΥ.Rows.Count - 1)
            DAapy.SelectCommand.Connection = CON1
            DAapy.Update(Ryth1)

p.s. there are about 300.000 rows with 13 columns so i don't know if ther is a memory problem
the code has some greek words

thnx

sorry...are you using SQL 2000 or 2005 for your data?
Visual Basic net 2003 with ACCESS
ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
There's no need to ask another question, and spend more points, on this issue.  So I suggest you delete

https://www.experts-exchange.com/questions/21976239/COPY-DATATABLE-DATA-FROM-DIFERENT-DATABASE-MDB-SOMETHING-LIKE-UPDATE.html

and I'll try and deal with the specific issues here.  You've already "paid" for an answer here.  I'm perfectly happy to work on any more details as an "after sales service".

I'm posting this immediately, as the sooner you go to delete the other question - if that's what you decide to do - the better.  I've got a copy of it and will draft a reply to it and post it here as soon as I can

Roger
A question about your tables.  Is the first field in each table a Primary Key and is its datatype AutoNumber?  The reason this is important is that, if you DELETE the shop1 records from the target table and then ADD the records from the source table to it you will end up with

3 SHOP3 CLIENT3 CAR2 20.000
4 SHOP4 CLIENT4 CAR2 20.000
5 SHOP1 CLIENT1 CAR1 20.000
6 SHOP1 CLIENT2 CAR1 20.000
7 SHOP1 CLIENT6 CAR2 20.000
8 SHOP1 CLIENT9 CAR2 20.000

That is, the two SHOP1 records (for CLIENT1 and CLIENT2) that were already there will change from having keys 1 and 2 to having keys 5 and 6.  Does that matter?  Because, if it does, it would be better to leave those SHOP1 records which already exist in the target table as they are, and just add any that are new.  

But then there is the question of how to tell which records are new.  The point here is that, if both tables have AutoNumber primary keys, each will be generating new numbers independently of each other.  So those generated for new SHOP1 records in the source table may already have been taken for other shops' records, in the target table.

None of these questions means that we can't - relatively simply - achieve what you want.  But they are points I need to understand before suggesting the best way to do it.

Roger
ARE YOU AN ANGEL OR SOMETHING :)

I USE THE KEY COL ONLY FOR SELECTING THEM ON MY PROGRAM DATASET1.TABLE(KEY).DELETE ETC
I DON'T CARE IF THERE IS A RANDOM NUMBER BUT AS FAR I KNOW IT CAN'T BE 2 SAME KEY'S SO I HAVE TO DO WITH CODE WHAT I DO BY MANUALLY
I DELETE THE ROWS FROM TARGET TABLES WITCH IS SHOP1 ROWS THEN I COPY / PASTE ALL COLUMNS EXCEPT THE ONE WITH THE KEY AND ACCESS GENERATES THE KEY AUTOMATICLY
 
SO YOUR RIGHT THAT IS A PROBLEM

ANY IDEA


THNX A LOT

P.S. I DELETED THE OTHER QUESTION
That is, the two SHOP1 records (for CLIENT1 and CLIENT2) that were already there will change from having keys 1 and 2 to having keys 5 and 6.  Does that matter?  Because, if it does, it would be better to leave those SHOP1 records which already exist in the target table as they are, and just add any that are new.  


I LIKE THIS IDEA TAKING ONLY THEN NEW DATA I CAN USE SQL TO SELECT ONLY THE DATA I WANT
SOMETHING LIKE ALL WHERE SHOP = SHOP1 AND > 1/1/2006
BUT I THINK IS MORE DIFICULT "FOR YOUR PART"
Try this

Imports System.Data.OleDb

    Private Sub RemoveOldRecords(ByVal targetFile As String, ByVal targetTable As String)

        Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & targetFile & ";User Id=admin;Password=;"
        Dim targetSQL As String = "SELECT * FROM " & targetTable & " WHERE SHOP = 'SHOP1'"

        Dim targetCon As New OleDbConnection(targetConStr)
        Dim targetDA As New OleDbDataAdapter(targetSQL, targetCon)
        Dim cb As New OleDbCommandBuilder(targetDA)
        Dim dt As New DataTable
        targetDA.Fill(dt)
        For Each dr As DataRow In dt.Rows
            dr.Delete()
        Next
        targetDA.Update(dt)

    End Sub

    Private Sub AddAllRecords(ByVal sourceFile As String, ByVal sourceTable As String, ByVal targetFile As String, ByVal targetTable As String)

        Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sourceFile & ";User Id=admin;Password=;"
        Dim sourceSQL As String = "SELECT * FROM " & sourceTable
        Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & targetFile & ";User Id=admin;Password=;"
        Dim targetSQL As String = "SELECT * FROM " & targetTable

        Dim targetCon As New OleDbConnection(targetConStr)
        Dim targetDA As New OleDbDataAdapter(targetSQL, targetCon)
        Dim cb As New OleDbCommandBuilder(targetDA)
        Dim targetCommand As OleDbCommand = cb.GetInsertCommand

        Dim sourceCon As New OleDbConnection(sourceConStr)
        Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
        targetCommand.Connection.Open()
        sourceCon.Open()
        Dim sourceReader As OleDbDataReader
        sourceReader = sourceCommand.ExecuteReader()
        While sourceReader.Read()
            'for each row from source
            For i As Integer = 1 To sourceReader.FieldCount - 1
                'load values into parameters ...
                '... EXCEPT field 0, which is AutoGenerated Primary Key
                targetCommand.Parameters(i).Value = sourceReader(i)
            Next
            targetCommand.ExecuteNonQuery()
        End While
        sourceReader.Close()
        sourceCon.Close()
        targetCommand.Connection.Close()

    End Sub

Make sure you have a back-up before doing so, as I haven't tested the code.

It's a two part process.  Run RemoveOldRecords first.  Then AddAllRecords.  The latter is more or less as I posted previously, but without the comments except for the one about the primary key, where I have made a slight alteration to the code.  I have used the terminology and field order as in your post.  If you simplified things for the purpose of your post, you will need to adapt the code accordingly.

Roger

PS.  I drafted the above before I saw your latest post.  If you would prefer to go down the other route, confirm that, and I'll make the necessary modifications.  But I won't now be able to do so for a few hours.
Sorry, there's a logic error in that code.  This line

                targetCommand.Parameters(i).Value = sourceReader(i)

should be replaced by

                targetCommand.Parameters(i).Value = sourceReader(i - 1)

The reader's values WILL include the PK from the sourcetable, but the Parameters won't inlcude a PK for the target table.

Roger
 
I even got that the wrong way round ;-(.  It should be

                targetCommand.Parameters(i - 1).Value = sourceReader(i)

Roger
Well u know what they say "programming is like sex if u do a mistake u have to suport it for the rest of ur life" :)

thnx a lot roger for eveything, i just got home and i will give it a try whatever u gave me and i will let u know

I like the update instead delete and copy paste, cause i don't know how long this will take there are A LOT of rows about 600.000 right now and it will get biger every day
and i also want u to ask is there any way i can add a progress bar into this code so the user can see how long this will take
i'm thinking between the While sourceReader.Read()
but if this take a little time 4get everything about update and progress bar .. i live this choice 2 you

you are the best, thnx again
A couple of variations to the earlier suggestions should do what you want.  The first one just involves changing the signature of the procedure and adding the date passed to it to the select statement for the source table - see the comments below.

    Private Sub AddNewRecords(ByVal sourceFile As String, ByVal sourceTable As String, ByVal targetFile As String, ByVal targetTable As String, ByVal After As DateTime) '<<< last argument is new

        Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sourceFile & ";User Id=admin;Password=;"
        Dim sourceSQL As String = "SELECT * FROM " & sourceTable & " WHERE  RecordDate > #" & After & "#" '<<< new, last argument used here
        Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & targetFile & ";User Id=admin;Password=;"
        Dim targetSQL As String = "SELECT * FROM " & targetTable

        Dim targetCon As New OleDbConnection(targetConStr)
        Dim targetDA As New OleDbDataAdapter(targetSQL, targetCon)
        Dim cb As New OleDbCommandBuilder(targetDA)
        Dim targetCommand As OleDbCommand = cb.GetInsertCommand

        Dim sourceCon As New OleDbConnection(sourceConStr)
        Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
        targetCommand.Connection.Open()
        sourceCon.Open()
        Dim sourceReader As OleDbDataReader
        sourceReader = sourceCommand.ExecuteReader()
        While sourceReader.Read()
            'for each row from source
            For i As Integer = 1 To sourceReader.FieldCount - 1
                'load values into parameters ...
                '... EXCEPT field 0, which is AutoGenerated Primary Key
                targetCommand.Parameters(i - 1).Value = sourceReader(i)
            Next
            targetCommand.ExecuteNonQuery()
        End While
        sourceReader.Close()
        sourceCon.Close()
        targetCommand.Connection.Close()

    End Sub

That will restrict the rows that are read from the source table to those which are "new": so that those are the only rows that will be added to the target table.  This assumes, however, that there is a date field - I have called it RecordDate in the above code - in the source table and (because the whole approach rests on the assumption that the source and target tables are identical) in the target table.

A progress bar will require (a) before the detailed reads begin, your program to know how many records will need to be read, so that you can set its .Maximum; and (b) an incrementing of the progress bar's .Value as the detailed reads are done.  You can fulfil requirement (a) by code on these lines

        Dim sourceCountSQL As String = "SELECT COUNT(*) FROM " & sourceTable & " WHERE  RecordDate > #" & After & "#"
        Dim sourceCountCommand As New OleDbCommand(sourceCountSQL, sourceCon)
        myProgressBar.Maximum = sourceCountCommand.ExecuteScalar

immediately after

        sourceCon.Open()

And (b) altering the value can be handled by something like

        myProgressBar.Value +=1

immediately after

            targetCommand.ExecuteNonQuery()

My previous warning still applies.  I have coded "onto the screen".  It is all untested.

Roger
I HAVE TRY THIS FOR ONE TABLE ONLY
CON1 = TARGET CONNECTION
CON2 = SOURCE CONNECTION


            Dim targetSQL As String = "DELETE * FROM &#913;&#928;&#933; WHERE &#922;&#945;&#964;&#940;&#963;&#964;&#951;&#956;&#945;='" & Katastima.Text & "'"
            Dim targetDA As New OleDbDataAdapter(targetSQL, CON1)
            Dim cb As New OleDbCommandBuilder(targetDA)
            Dim dt As New Data.DataTable
            Dim dr As Data.DataRow
            targetDA.Fill(dt)
            For Each dr In dt.Rows
                dr.Delete()
            Next
            targetDA.Update(dt)



            Dim sourceSQL2 As String = "SELECT * FROM &#913;&#928;&#933; WHERE &#922;&#945;&#964;&#940;&#963;&#964;&#951;&#956;&#945;='" & Katastima.Text & "'"
            Dim targetSQL2 As String = "SELECT * FROM &#913;&#928;&#933; WHERE &#922;&#945;&#964;&#940;&#963;&#964;&#951;&#956;&#945;='" & Katastima.Text & "'"


            Dim targetDA2 As New OleDbDataAdapter(targetSQL, CON1)
            Dim cb2 As New OleDbCommandBuilder(targetDA2)
            Dim targetCommand As OleDbCommand = cb2.GetInsertCommand  ' <<<<<<<< ERROR LINE  >>>>>>>>>> LOOK IN THE END

            Dim sourceCommand As New OleDbCommand(sourceSQL2, CON2)
            targetCommand.Connection.Open()
            'sourceCon.Open() I ALLREADY OPEN THIS CONNECTION
            Dim sourceReader As OleDbDataReader
            sourceReader = sourceCommand.ExecuteReader()
            While sourceReader.Read()
                'for each row from source
                For i As Integer = 1 To sourceReader.FieldCount - 1
                    'load values into parameters ...
                    '... EXCEPT field 0, which is AutoGenerated Primary Key
                    targetCommand.Parameters(i - 1).Value = sourceReader(i)
                Next
                targetCommand.ExecuteNonQuery()
            End While
            sourceReader.Close()
            CON2.Close()
            targetCommand.Connection.Close()

I GET AN ERROR Additional information: Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.

ANY IDEAS

DIMITRIS
i fix that error now i have another one

5 lines befroe the end (targetCommand.Parameters(i - 1).Value = sourceReader(i))

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
I hate that error message.  It means we have to dig to find out what, specifically, the error is.

But, before doing any of that, it looks to me as though there may be an error in your code.  In this line

            Dim targetDA2 As New OleDbDataAdapter(targetSQL, CON1)

your are using targetSQL, whereas you should be using targetSQL2.  It may be that that is the error you have already identified and resolved.

If that is so, we need to check exactly what the .CommandText of the target command is and then compare it with what the datareader is producing.  Stick this line

            Debug.WriteLine(targetCommand.CommandText)

immediately after

            Dim targetCommand As OleDbCommand = cb2.GetInsertCommand

That will show what fields the command is trying to fill and, most importantly, the order in which the parameters (hence values) for those fields, are supposed to appear in the datareader's output.

Then (temporarily) comment out this code

                    targetCommand.Parameters(i - 1).Value = sourceReader(i)

and put this code in its place

                    Debug.WriteLine(sourceReader.GetName(i))

Then put

                Stop

between

                Next

and

                targetCommand.ExecuteNonQuery()
 
Those lines should give you a list, in the order in which the values are being offered to the target command's parameters, of the fields from the source from which those values are coming.

That may enable you to sort out the problem.  If not, post the debug output here.

Roger
the error now is "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll"

in targetCommand.ExecuteNonQuery() line

Roger is there any way we can talk this from a chat msn yahoo mirc
I think we will get over this problem faster
what do you say

tell me the time and the place i'll be there
Dimitris

Sorry, I don't do chat rooms.

Did you put this line

            Debug.WriteLine(targetCommand.CommandText)

in?  What did the output say?  And this line?

                    Debug.WriteLine(sourceReader.GetName(i))

And what did the output for that say?

Roger
INSERT INTO &#913;&#928;&#933;( &#922;&#945;&#964;&#940;&#963;&#964;&#951;&#956;&#945; , &#928;&#945;&#961;&#945;&#963;&#964;&#945;&#964;&#953;&#954;&#972; , &#919;&#956;&#949;&#961;&#959;&#956;&#951;&#957;&#943;&#945; , &#922;&#969;&#948;&#953;&#954;&#972;&#962; &#960;&#949;&#955;&#940;&#964;&#951; , &#917;&#960;&#974;&#957;&#965;&#956;&#959; , &#908;&#957;&#959;&#956;&#945; , &#936;&#949;&#965;&#948;&#974;&#957;&#965;&#956;&#959; , &#933;&#960;&#951;&#961;&#949;&#963;&#943;&#945; , &#917;&#953;&#948;&#953;&#954;&#942; &#941;&#954;&#960;&#964;&#969;&#963;&#951; , &#932;&#953;&#956;&#942; , &#904;&#954;&#960;&#964;&#969;&#963;&#951; , &#934;&#928;&#913; , &#913;&#958;&#943;&#945; % &#963;&#965;&#957;&#949;&#961;&#947;&#940;&#964;&#951; , &#932;&#961;&#972;&#960;&#959;&#962; &#960;&#955;&#951;&#961;&#969;&#956;&#942;&#962; , &#932;&#973;&#960;&#959;&#962; ) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )
&#922;&#945;&#964;&#940;&#963;&#964;&#951;&#956;&#945;
&#928;&#945;&#961;&#945;&#963;&#964;&#945;&#964;&#953;&#954;&#972;
&#919;&#956;&#949;&#961;&#959;&#956;&#951;&#957;&#943;&#945;
&#922;&#969;&#948;&#953;&#954;&#972;&#962; &#960;&#949;&#955;&#940;&#964;&#951;
&#917;&#960;&#974;&#957;&#965;&#956;&#959;
&#908;&#957;&#959;&#956;&#945;
&#936;&#949;&#965;&#948;&#974;&#957;&#965;&#956;&#959;
&#933;&#960;&#951;&#961;&#949;&#963;&#943;&#945;
&#917;&#953;&#948;&#953;&#954;&#942; &#941;&#954;&#960;&#964;&#969;&#963;&#951;
&#932;&#953;&#956;&#942;
&#904;&#954;&#960;&#964;&#969;&#963;&#951;
&#934;&#928;&#913;
&#913;&#958;&#943;&#945; % &#963;&#965;&#957;&#949;&#961;&#947;&#940;&#964;&#951;
&#932;&#961;&#972;&#960;&#959;&#962; &#960;&#955;&#951;&#961;&#969;&#956;&#942;&#962;
&#932;&#973;&#960;&#959;&#962;
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll


the line that stop is "targetCommand.ExecuteNonQuery()"

many words are in greek lang
It's difficult to be sure with the coding for the Greek characters, but it looks to me as though some of your field names have spaces in them.  This one

&#922;&#969;&#948;&#953;&#954;&#972;&#962; &#960;&#949;&#955;&#940;&#964;&#951;

has a space between &#962; and &#960;

This one

&#917;&#953;&#948;&#953;&#954;&#942; &#941;&#954;&#960;&#964;&#969;&#963;&#951;

has a space between &#942; and &#941;

And this one

&#913;&#958;&#943;&#945; % &#963;&#965;&#957;&#949;&#961;&#947;&#940;&#964;&#951;

has spaces between &#945; and % and again between % and &#963;

If that appearance is correct, it will throw the error you are reporting.  Ideally, you should avoid spaces in field names in Access tables but, if you are going to use them, you will need to enclose them in square brackets in any SQL statements.  Given that, here, we are trying to use the automatic generation of the SQL statements, it would certainly be better if you could alter the Access table names to get rid of spaces.

Is my interpretation of the code you posted correct in this respect?  And, if so, can you deal with it in the Access tables themselves?

I'm also not too happy (although I'm not sure that it actually breaks "the rules") with that " % " appearing in the middle of a field name.

Roger
I thought this (removing spaces) before but i ahve to change to much things
i have to reinstall the program to all shops change the code in 21 forms recreate the datatables datasets
it's to much work and probably i will get milions of errors, is there any way i can do this without removing spaces

thnx roger


Dimitris
or should i just remove the % symbol from one field

Try putting this line

  targetCommand.CommandText = "INSERT INTO &#913;&#928;&#933;( [&#922;&#945;&#964;&#940;&#963;&#964;&#951;&#956;&#945;] , [&#928;&#945;&#961;&#945;&#963;&#964;&#945;&#964;&#953;&#954;&#972;] , [&#919;&#956;&#949;&#961;&#959;&#956;&#951;&#957;&#943;&#945;] , [&#922;&#969;&#948;&#953;&#954;&#972;&#962; &#960;&#949;&#955;&#940;&#964;&#951;] , [&#917;&#960;&#974;&#957;&#965;&#956;&#959;] , [&#908;&#957;&#959;&#956;&#945;] , &#936;&#949;&#965;&#948;&#974;&#957;&#965;&#956;&#959; , &#933;&#960;&#951;&#961;&#949;&#963;&#943;&#945; , [&#917;&#953;&#948;&#953;&#954;&#942; &#941;&#954;&#960;&#964;&#969;&#963;&#951;] , [&#932;&#953;&#956;&#942;] , [&#904;&#954;&#960;&#964;&#969;&#963;&#951;] , [&#934;&#928;&#913;] , [&#913;&#958;&#943;&#945; % &#963;&#965;&#957;&#949;&#961;&#947;&#940;&#964;&#951;] , [&#932;&#961;&#972;&#960;&#959;&#962; &#960;&#955;&#951;&#961;&#969;&#956;&#942;&#962;] , [&#932;&#973;&#960;&#959;&#962;] ) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )"

immediately after

        Dim targetCommand As OleDbCommand = cb.GetInsertCommand

What that tries to do is put square brackets round all the field names in the SQL statement.  Check it see if it looks like I have got it right.  It means that the SQL statement will be "hard coded", rather than being dynamically generated, but it is worth testing to see if it overcomes the problem.  If it does, then you can use it.  But you will need to remember that you will then have to alter this code in your app if you subsequently make any change to the Access tables.

Roger
i give u the links because the language so you can read it

i have try to put all field in [] but still nothing
On the code as shown in

http://12345.gr/arxeia/roger.jpg

you would.  You have left the line

                    targetCommand.Parameters(i - 1).Value = sourceReader(i)

commented out.

Roger
GOOD NEWS

I REMOVED ALL THE ADDS YOU TELL ME TO DO TO FIND THE ERROR IN DEBUG AND RESTORE THE OTHER LINES
AND IT WORKED FINE :)

THANK YOU VERY MUCH ROGER 4 YOUR TIME AND EVERYTHING
YOU ARE THE BEST
IF YOU NEED ANYTHING FROM ME LET ME KNOW

DIMITRIS
roger one last question is there any way i can add the [] automaticly in every field so i dont have to make the "insert into" for every table


Dimitris
this is the final code that works fine

http://12345.gr/arxeia/roger2.jpg
Try this

        Dim targetCommand As OleDbCommand = cb.GetInsertCommand
        Dim cmdText as String = targetCommand.CommandText
        'put [ before first field names
        cmdText = cmdText.Replace("( ", "( [")
        'put ] after and [ before all intermediate field names
        cmdText = cmdText.Replace(" , ", "] , [")
        'put ] after last field name
        cmdText = cmdText.Replace(" )", "] )")
        targetCommand.CommandText = cmdText

You'll have to go back to the old code to get the autogenerated commandtext and then alter that

Roger
Correction

        Dim targetCommand As OleDbCommand = cb.GetInsertCommand
        Dim cmdText as String = targetCommand.CommandText
        'put [ before first field names
        cmdText = cmdText.Replace("( ", "( [")
        'put ] after and [ before all intermediate field names
        cmdText = cmdText.Replace(" , ", "] , [")
        'put ] after last field name
        cmdText = cmdText.Replace(" )", "] )")
        'remove [ and ] from parameters in value clause
        cmdText = cmdText.Replace("[?]", "?")
        targetCommand.CommandText = cmdText

Roger

Roger now i have another problem some of the tables have 2 words(space) like the problem we had with the fields

this is the code i have now

http://12345.gr/arxeia/roger3.jpg

thnx Dimitris
Dimitris

What you are going to have to do, if you are not willing or able to change things in your Access database, is write code to add the necessary square brackets round the table names (as well as round the field names) in any command text which is autogenerated by a commandbuilder.  The code that I have already supplied to do the field names illustrates the technique

    statement = statement.Replace(oldContent, newContent)

If you revise your sub so as to pass to it the existing table name instead of (or as well as) a statement you can simply substitute that for oldcontent and "[" & oldcontent & "]" for newcontent in the above pattern.

You said, a few posts ago, (EMPHASIS added) "ONE last question ...".  I think, now, that I've fully answered your original question, plus a number of supplementaries.  I feel that, if you have any more problems/questions, you should raise them as new, specific questions.

Roger
ok i will try that roger
cause i can't change the access db 4 many reasons

P.S i didn't meant to bother u i just asked you again cause u know exactly my problem
sorry & thnx 4 everything

this is my last message