How to Select

Hey,

I am a new member of this site. I seem to have a problem that I can't get around:
I have 2 databases(MS Access). I wish to copy the contents of a table in the first database to the second. The schemas has been statically created so it is just the records of the table that I have to paste.
Which would be the best way to do it?

-Saumil Annegiri

Comments

  • [b][red]This message was edited by raymcd at 2003-5-26 13:30:38[/red][/b][hr]
    : Hey,
    :
    : I am a new member of this site. I seem to have a problem that I can't get around:
    : I have 2 databases(MS Access). I wish to copy the contents of a table in the first database to the second. The schemas has been statically created so it is just the records of the table that I have to paste.
    : Which would be the best way to do it?
    :
    : -Saumil Annegiri
    :
    :
    You could probably just copy and paste. If that doesn't work, export the table to a text file and import the file on the other db.

    -ray


  • : [b][red]This message was edited by raymcd at 2003-5-26 13:30:38[/red][/b][hr]
    : : Hey,
    : :
    : : I am a new member of this site. I seem to have a problem that I can't get around:
    : : I have 2 databases(MS Access). I wish to copy the contents of a table in the first database to the second. The schemas has been statically created so it is just the records of the table that I have to paste.
    : : Which would be the best way to do it?
    : :
    : : -Saumil Annegiri
    : :
    : :
    : You could probably just copy and paste. If that doesn't work, export the table to a text file and import the file on the other db.
    :
    : -ray
    :
    Geez I am slow today... All you need to do is create a linked table and perform an append query from the source table to the destination table.

    -ray

  • I really could not follow what you meant by linked tables et.al. And the first option would not work because I have to copy the data from tableX in DBase1 to a tableX in DBase2(will have different connection strings) at runtime. However their schemas are identical and created before runtime.

    I was more thinking on the lines of:
    Copying all the data in tableX (DBase1) to a dataset. Then using this dataset to insert data in tableX (DBase2) - (different connections string). Do you know how to go about this?

    -Saumil.


    : : [b][red]This message was edited by raymcd at 2003-5-26 13:30:38[/red][/b][hr]
    : : : Hey,
    : : :
    : : : I am a new member of this site. I seem to have a problem that I can't get around:
    : : : I have 2 databases(MS Access). I wish to copy the contents of a table in the first database to the second. The schemas has been statically created so it is just the records of the table that I have to paste.
    : : : Which would be the best way to do it?
    : : :
    : : : -Saumil Annegiri
    : : :
    : : :
    : : You could probably just copy and paste. If that doesn't work, export the table to a text file and import the file on the other db.
    : :
    : : -ray
    : :
    : Geez I am slow today... All you need to do is create a linked table and perform an append query from the source table to the destination table.
    :
    : -ray
    :
    :



  • [b][red]This message was edited by raymcd at 2003-5-27 11:6:39[/red][/b][hr]
    To link a table, open the database in access (you could probably do this from code but I am not sure how). Click File|GetExternalData|LinkTable. This will begin the process of linking the tables from one database to another. If you play around with this, you'll figure it out fast enough. Once a table has been linked to, you can use it as if it were inside the database where it is linked. If you link all the tables, you could just use one connection. It'll make the coding more readable and more maintainable.

    To do it the way you suggested, you could probaly use Command.ExecuteReader() to get a DataReader pointing to the Source table. Of course you have a connection object pointing to the Source database. Then you could just loop forward and insert each with a command.executenonquery against a connection object pointing to the destination database.

    The choice here is programmer preference. Whatever you like go with. If this were SQL server, the choice would be linked tables without hesitation but in access its usually all local db's not networked. If it is networked (IE the mdb's are not on the machine running the code) you should link the tables. This is because you don't want to pull all the data across the network to the client (1 trip) and send it back (2 trips). If both MDB are on the same server its much faster (0 trips across the net).

    -ray

    : I really could not follow what you meant by linked tables et.al. And the first option would not work because I have to copy the data from tableX in DBase1 to a tableX in DBase2(will have different connection strings) at runtime. However their schemas are identical and created before runtime.
    :
    : I was more thinking on the lines of:
    : Copying all the data in tableX (DBase1) to a dataset. Then using this dataset to insert data in tableX (DBase2) - (different connections string). Do you know how to go about this?
    :
    : -Saumil.
    :
    :
    : : : [b][red]This message was edited by raymcd at 2003-5-26 13:30:38[/red][/b][hr]
    : : : : Hey,
    : : : :
    : : : : I am a new member of this site. I seem to have a problem that I can't get around:
    : : : : I have 2 databases(MS Access). I wish to copy the contents of a table in the first database to the second. The schemas has been statically created so it is just the records of the table that I have to paste.
    : : : : Which would be the best way to do it?
    : : : :
    : : : : -Saumil Annegiri
    : : : :
    : : : :
    : : : You could probably just copy and paste. If that doesn't work, export the table to a text file and import the file on the other db.
    : : :
    : : : -ray
    : : :
    : : Geez I am slow today... All you need to do is create a linked table and perform an append query from the source table to the destination table.
    : :
    : : -ray
    : :
    : :
    :
    :
    :
    :



  • Thanks Ray.

    The Linked tables approach sounds better. Since the DBases will be on the same server. However the application has to dynamically figure out which DBase to copy it to from the primary DBase.(Since there are 5 DBase options).

    A concern I have is to use the second approach (DataReader), I would have to loop through all records and there would be a ExecuteNonQuery(Insert) for each iteration. Would this not screw it up bad?
    I mean is there a way to directly to copy the whole dataset to the new DBase without having to iterate through a record individually?

    -Saumil.



    : [b][red]This message was edited by raymcd at 2003-5-27 11:6:39[/red][/b][hr]
    : To link a table, open the database in access (you could probably do this from code but I am not sure how). Click File|GetExternalData|LinkTable. This will begin the process of linking the tables from one database to another. If you play around with this, you'll figure it out fast enough. Once a table has been linked to, you can use it as if it were inside the database where it is linked. If you link all the tables, you could just use one connection. It'll make the coding more readable and more maintainable.
    :
    : To do it the way you suggested, you could probaly use Command.ExecuteReader() to get a DataReader pointing to the Source table. Of course you have a connection object pointing to the Source database. Then you could just loop forward and insert each with a command.executenonquery against a connection object pointing to the destination database.
    :
    : The choice here is programmer preference. Whatever you like go with. If this were SQL server, the choice would be linked tables without hesitation but in access its usually all local db's not networked. If it is networked (IE the mdb's are not on the machine running the code) you should link the tables. This is because you don't want to pull all the data across the network to the client (1 trip) and send it back (2 trips). If both MDB are on the same server its much faster (0 trips across the net).
    :
    : -ray
    :
    : : I really could not follow what you meant by linked tables et.al. And the first option would not work because I have to copy the data from tableX in DBase1 to a tableX in DBase2(will have different connection strings) at runtime. However their schemas are identical and created before runtime.
    : :
    : : I was more thinking on the lines of:
    : : Copying all the data in tableX (DBase1) to a dataset. Then using this dataset to insert data in tableX (DBase2) - (different connections string). Do you know how to go about this?
    : :
    : : -Saumil.
    : :
    : :
    : : : : [b][red]This message was edited by raymcd at 2003-5-26 13:30:38[/red][/b][hr]
    : : : : : Hey,
    : : : : :
    : : : : : I am a new member of this site. I seem to have a problem that I can't get around:
    : : : : : I have 2 databases(MS Access). I wish to copy the contents of a table in the first database to the second. The schemas has been statically created so it is just the records of the table that I have to paste.
    : : : : : Which would be the best way to do it?
    : : : : :
    : : : : : -Saumil Annegiri
    : : : : :
    : : : : :
    : : : : You could probably just copy and paste. If that doesn't work, export the table to a text file and import the file on the other db.
    : : : :
    : : : : -ray
    : : : :
    : : : Geez I am slow today... All you need to do is create a linked table and perform an append query from the source table to the destination table.
    : : :
    : : : -ray
    : : :
    : : :
    : :
    : :
    : :
    : :
    :
    :
    :
    :



  • If you have 5 different db options, just rename the linked tables. I would name them tableabc1, tableabc2, tableabc3, tableabc4, tableabc5, tablexyz1, tablexyz2... This does not affect the real table names, only the name of the link. This is sort of like an alias.

    Then you could easily do something like...

    [code]x = GetDatabaseOption() 'returns 1-5
    strSQL = "insert into tableabc" & Trim(x.tostring()) & " values(Select * from tablewhatever)"[/code]

    As for the concern about screwing it up bad with by looping through a datareader... Yes, you can always cause problems if you do things incorrectly. This is universal though. You are always at risk of updating too many rows, or deleting too many rows, etc... This is no different in the link option above. That option has risks as well. This is just one more reason to thoroughly test all the code you ever use. To be completely honest, neither option here is all that risky. Both are fairly standard database interaction.

    -ray

    : Thanks Ray.
    :
    : The Linked tables approach sounds better. Since the DBases will be on the same server. However the application has to dynamically figure out which DBase to copy it to from the primary DBase.(Since there are 5 DBase options).
    :
    : A concern I have is to use the second approach (DataReader), I would have to loop through all records and there would be a ExecuteNonQuery(Insert) for each iteration. Would this not screw it up bad?
    : I mean is there a way to directly to copy the whole dataset to the new DBase without having to iterate through a record individually?
    :
    : -Saumil.
    :
    :
    :
    : : [b][red]This message was edited by raymcd at 2003-5-27 11:6:39[/red][/b][hr]
    : : To link a table, open the database in access (you could probably do this from code but I am not sure how). Click File|GetExternalData|LinkTable. This will begin the process of linking the tables from one database to another. If you play around with this, you'll figure it out fast enough. Once a table has been linked to, you can use it as if it were inside the database where it is linked. If you link all the tables, you could just use one connection. It'll make the coding more readable and more maintainable.
    : :
    : : To do it the way you suggested, you could probaly use Command.ExecuteReader() to get a DataReader pointing to the Source table. Of course you have a connection object pointing to the Source database. Then you could just loop forward and insert each with a command.executenonquery against a connection object pointing to the destination database.
    : :
    : : The choice here is programmer preference. Whatever you like go with. If this were SQL server, the choice would be linked tables without hesitation but in access its usually all local db's not networked. If it is networked (IE the mdb's are not on the machine running the code) you should link the tables. This is because you don't want to pull all the data across the network to the client (1 trip) and send it back (2 trips). If both MDB are on the same server its much faster (0 trips across the net).
    : :
    : : -ray
    : :
    : : : I really could not follow what you meant by linked tables et.al. And the first option would not work because I have to copy the data from tableX in DBase1 to a tableX in DBase2(will have different connection strings) at runtime. However their schemas are identical and created before runtime.
    : : :
    : : : I was more thinking on the lines of:
    : : : Copying all the data in tableX (DBase1) to a dataset. Then using this dataset to insert data in tableX (DBase2) - (different connections string). Do you know how to go about this?
    : : :
    : : : -Saumil.
    : : :
    : : :
    : : : : : [b][red]This message was edited by raymcd at 2003-5-26 13:30:38[/red][/b][hr]
    : : : : : : Hey,
    : : : : : :
    : : : : : : I am a new member of this site. I seem to have a problem that I can't get around:
    : : : : : : I have 2 databases(MS Access). I wish to copy the contents of a table in the first database to the second. The schemas has been statically created so it is just the records of the table that I have to paste.
    : : : : : : Which would be the best way to do it?
    : : : : : :
    : : : : : : -Saumil Annegiri
    : : : : : :
    : : : : : :
    : : : : : You could probably just copy and paste. If that doesn't work, export the table to a text file and import the file on the other db.
    : : : : :
    : : : : : -ray
    : : : : :
    : : : : Geez I am slow today... All you need to do is create a linked table and perform an append query from the source table to the destination table.
    : : : :
    : : : : -ray
    : : : :
    : : : :
    : : :
    : : :
    : : :
    : : :
    : :
    : :
    : :
    : :
    :
    :
    :
    :

  • Did you try choosing from File menu > Get external data ?
    click import and files of type "Microsoft Access". Choose a database and it will open it and show you all the objects you can import including tables.

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories