Importing from another db

[Numpty]

From what I have read on this subject so far it would appear that to import data from another db the table structure, field names, data types etc have to be exactly the same.

Is there no way around this? Perhaps using a query that can select information from a tables field in one db then transfer that into another db and change the name, data type etc?

Long shot probably but I thought I'd ask!!

 

[Fizzio]

That is not strictly true as you can use the append query to 'import' data from one table to another. Create a link t the table you want to import and use the append query to copy the data from one table to another. Field Names do not matter but data type does.

If you want to change the data type, you are better served using a make table query.

You can get clever with code and use ADO to remotely connect to a Db, and dynamically populate one table from another table on different Db's but if you do not need automation, the first 2 options should suffice.

I'm sure someone will let me know how wrong I am though!

 

[Numpty]

OK, cheers for that.

I'm off to have a looksee at the db they want to get data from, I'll look into what you have said when I get back and I'll have a go!

I'm sure I'll be back asking for help though!

 

[Numpty]

I'm back!!

OK, the datbase they want me to import from has it's data set up in a completely different way.

Table: Registration

RegistrationID - Autonumber
ProviderName
ProvAddressID - Linked to Table: Address
TelStd
TelNum
FaxStd
FaxNum
EmailAddress

Table: Address

AddressID - AutoNumber
HouseNum
Street
ExtraAddress
VillageTownID
PostalTownID
CountyID
PostCode

I need to transfer the data held in that way to populate a table in another database

Table: Settings

SettingID - AutoNumber
SettingName
AddressLine1
AddressLine2
AddressLine3
Town
County
PostCode
TelNumber
FaxNumber

Not being anywhere near an expert with access I cannot begin to think how an append query will work in transferring this data. Yes I can see how I could transfer the ProviderName into SettingName but seeing as the address fields are just number fields rather than text it would not transfer the right data.

I wouldn't have a clue where to start using DAO ADO either. Does anyone have any suggestions as to how this can be done?

It's only a one off transfer.

Cheers

 

[modest]

Request Clarification

First let me understand what you're asking. You're trying to convert 2 tables to 1 table, correct? By turning the following two tables:

[FONT=Courier New]
[B]
Table: Registration                     Table: Address[/B]
RegistrationID - Autonumber             AddressID - AutoNumber
ProviderName                            HouseNum
ProvAddressID - FK AddressID            Street
TelStd                                  ExtraAddress
TelNum                                  VillageTownID
FaxStd                                  PostalTownID
FaxNum                                  CountyID
EmailAddress                            PostCode

[I]into:[/I]

[B]Table: Settings[/B]
SettingID - AutoNumber
SettingName
AddressLine1
AddressLine2
AddressLine3
Town
County
PostCode
TelNumber
FaxNumber

I don't think you're going to need ADO or DAO if these are going to be both Access tables. Access's append query gives the ability to transfer to another file... or transfer within the same file.

Furthermore, you must do a join to setting the ProvAddressID (from Table:Registration) equal to the AddressID (from Table:Address). Then you can append Street, Town, and County from Address to the Settings table.

It's bad db-design to label your towns/counties using an ID (number). You don't need that. And if the issue is that your towns/counties from your Address table is a number and in your Settings table they're not... there are other ways to do this, which I don't won't get into. I would first make sure that towns/counties are not FKs of other tables (they must be labeled somewhere by name)
[/FONT]