Automate Copying Fields

[Rob Sieggs]

Hello,

Our company has an ecommerce website and we also sell on eBay. We are looking for a way to automate listing products on eBay that are already on our website and keeping pricing and inventory consistent.

We have two Excel files that have been converted into tables in an Access 2003 database. One is the inventory of our products on our website and the other table contains the required fields to import into eBay. Many of the fields in each table contain the same information about each product but the field names are different, e.g. Product SKU and Product Number. We are looking for the most efficient way to automate coping over these fields as we have several thousand products.

Is this something that can be done with simple macros or would modules with VBA code be required?

[Mihail]

Hi Rob !
Welcome to the forum !

Sorry, but to convert the Excel sheets into tables is one thing and to obtain a database from this tables is a total different thing.

I think that is better to do the job in Excel (maybe because I know better Excel than Access) then convert into table(s).

On the other hand, what you wish to copy ? And what you wish to obtain ? A new set of records ? An updated table using records from the second one ?

Come back with a fully set of information about what you wish to do.

[NeoPa]

In Access simply set up a query of the table and assign ALIASes to the fields with different names from the ones required.

EG.
Fields required by eCommerce site :
ProductName
SellPrice
Colour

Fields required by eBay :
ProductName
SalePrice
ProdColour

Table Name = [tblWeb]

[I][B][U]Field Name     Type[/U][/B][/I]
ProductName    String
SellPrice      Currency
Colour         String

Query Name = [qryeBay]

SELECT [ProductName]
     , [SalePrice] AS [SellPrice]
     , [Colour] AS [ProdColour]
FROM   [tblWeb]

Such ALIASing can also be handled in Design View of a query by using :

SellPrice: [SalePrice]
ProdColour: [Colour]

[Rob Sieggs]

Please allow me to clarify.

The table downloaded from our website is actually a "linked" table. This way, we can download the file each day and simply upload the link to the Access database. The second table was created in Access and contains all the fields required by eBay when uploading our products for posting on eBay. The tables are both joined by the Product SKU.

At this point, I am thinking we need some sort of Macro or code to open both files, and copy the contents of the required fields from our website table to the eBay table. What about a command like For Each Record, copy these fields to this Table?

[NeoPa]

Of course you're allowed to clarify at any time, but we prefer you do so in the original question. It doesn't seem too onerous a requirement and can save much wasted time. Unfortunately, it seems clarity is still some way off.

Is one of the tables a master or something, such that you'd overwrite details of one table with the other? How about some details so that we have something to work from.

[Rob Sieggs]

The eBay table is really just a blank table with the required field names that are needed for a successful upload of our products. Some of the fields for each record will always be the same. For example, the field PayPalAccepted is a yes/no value that will always be "yes".

So again, we're looking for an automated solution in which Access can look at values for certain fields and copy them from the linked table to the eBay table. Then we save or export the eBay table and we're good to go. Or, we could do the same with a query.

I posted my clarification above responding to Mihail and prior to reading your suggestion, NeoPa, about the ALIAS field names in a query. This might work since a lot of the fields have the same values but the field names are different. As long as the final output uses the ALIAS field names.

Thank you for your help.

[NeoPa]

Ah. I see. That certainly makes more sense if considered in that context :-)

Yes. The final output would use the ALIAS names.