Please help me to solve this?

[bluenose76]

Hi,

I posted a message in the macros section requesting some help.

what I would like to do is change the column names of a table after importing data from an excel CSV file.

Rickster57 was kind enought o reply and gave me this bit of code

currentdb.TableDefs("table1").Fields("OldFieldName ").Name = "NewFieldName"

unfortunately my knowledge does not help me mucha dn i cant figure out how to get this to work in a new module?

my intention is that once the module works then i shal call it from my macro to change the names at the end of everything else.

can someone please give me an example of what i would need to do to get a working module that will change my column names in a table.

Thank you all in advance

Bev

 

[Anthony George]

Do You Know How To Create A Command Button

Hi

Rather than using macro's or modules to do this job, why don't you use a button on a form, create an on-click event procedure, and add the bit of code that you have been given into the subroutine.

If none of the above makes any sense at all, please say so and I will try and write you someting that will steer you in the right direction.

Kindest regards

Tony

 

[bluenose76]

Thank you for your reply.

what i am doing is importing a large ammount of records from an existing CSV file that has been exported from another program.

to do this I have created a Macro that clears out my table completely. and then imports from the file on my hard drive.

By default when imported, the column names are F1 F2 F3 etc etc and i cant change this in the CSV as that is the output format.

I am trying to make the whole process transparrent to the end user.
so whent hey open my DB they see a form and there is a button called import. this is linked to the macro so that it clears the table, imports the new data. the problem i currently have is that i run query's and at present the results are in columns called F1 F2 etc etc.

so i would liek to rename the columns at the end of my Macro so that the result of the Query are the correct column names. I know i can create a report that will show what i wish but i would preferto stick to the Query.

Hope you can help
Thanks
Bev

 

[Anthony George]

Can you send me a copy of the Macro

Hi Bev
Can you either post a copy of the Macro, or a copy of the database so that i can offer a solution?
Regards
Tony

 

[bluenose76]

The only thing that I have in my macro is:

DeleteObject (this deletes the table)
Transfer Text (brings in the data from CSV File)
MsgBox (gives end user a confirmation it has done)


That is it.

Bev

 

[Anthony George]

Back to basics

Ok Bev

thanks for that, going back to my original question?

Rather than using macro's or modules to do this job, why don't you use a button on a form, create an on-click event procedure, and add the bit of code that you have been given into the subroutine.

If none of the above makes any sense at all, please say so and I will try and write you someting that will steer you in the right direction.

Do you understand the above question?

I need to know this, because I need to know how detailed my reply must be.

Regards

Tony

 

[bluenose76]

I am happy with the on click event procedure and how to add them, i am not sure what i would add though in order to achive what i wish.

I could add the on click event procedure to the same button on the form that invokes the macro.

 

[Anthony George]

Ok I will have a look for you

Hi Bev

I'm in the Uk and it is now 1210 Pm and i need some sleep.

I promise I will have a look over the weekend and post you a reply asap.

If someone else posts a reply please have a look at it, but I will attempt to resolve your problem for you as so many others on this forum have done for me in the past.

Kindest regards

Tony

Ps Good night and God Bless

 

[bluenose76]

Tony,

Thank you, I too am int eh uk and need some sleep also.

I look forward to your reply

Bev

 

[Anthony George]

Hope this is of some help

Hi Bev

Insert the following code into a Command button's on click event (i've used a button caled Command38)

The first row of the code deletes the old table Table1 (note! the first time you run this, an old table 1 must exist, any old

table will do even an empty table with just one field, so long as it is called Table1)

The second row of the code imports the file (My file is called MyExcelFile.csv, in a folder called aMacrotest, on the C

drive, obviously you can use any file,table,and folder names that you want so long as they match with the rest of the code)

The other 4 code rows rename the column headings.


Here is the Code:


Private Sub Command38_Click()
On Error GoTo Err_Command38_Click


DoCmd.DeleteObject acTable, "Table1"

DoCmd.TransferText acImportDelim, , "Table1", "C:\aMacroTest\MyExcelFile.csv", False

CurrentDb.TableDefs("Table1").Fields("F1").Name = "Make"
CurrentDb.TableDefs("Table1").Fields("F2").Name = "Product"
CurrentDb.TableDefs("Table1").Fields("F3").Name = "Packing"
CurrentDb.TableDefs("Table1").Fields("F4").Name = "Qty"

Exit_Command38_Click:
Exit Sub

Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub



This is quite a simplified solution and you do need to know how many columns there are in the table in advance and also the

original column headings.

(nb when entering the above code, make sure that all of the commas and spaces are the same)




I copied this next bit from a post on the net and you might want to play with this as well:-




For this to work You need to set up an import specification, then use this in the TransferText command.

To build a specification:-

from the Table Tab in the Database window, right click, Link Table, select CSV, find the file, OK

An importing wizard is launched. In the bottom left is a button 'Advanced' - select this

A new dialog opens, which is pretty self explanatory - you can select columns, data types etc, then save as a specification.

Take a note of the specification - lets say this is called "ABC Link Specification".

Now in your VBA use that as follows:

DoCmd.TransferText acImportDelim, "ABC Link Specification", "tblCSVload", "C:\DATABASE\FRONTIERIMPORT.CSV", False

The objective of all of the above is to force a data type, rather then letting Access make a guess.

I Hope this helps

Kind regards

Tony

 

[bluenose76]

Tony,

Sorry i have not replied earlier, Thankyou for your solution, I shall try this and see how i get on.

I shall let you know if this works for me or not.

Thanks

Bev

 

[IR_Moon]

Alternatively instead of deleting your whole table, you could just delete the data with a query or code, then when you are importing, don't import the header row with the names in it - This will keep the old field names, no?

Docmd.RunSQL "Delete * from tTable1"

'instead of 
DoCmd.DeleteObject acTable, "Table1"

 

[bluenose76]

Tony,

I have tried your solutiona nd am pleased to say that it has worked for me

Thank you.

I really must start learning more coding instead of relying upon the front end!

Thank you again.

Bev

 

[bluenose76]

Alternatively instead of deleting your whole table, you could just delete the data with a query or code, then when you are importing, don't import the header row with the names in it - This will keep the old field names, no?

Docmd.RunSQL "Delete * from tTable1"

'instead of 
DoCmd.DeleteObject acTable, "Table1"

IR MOON,

Thank you for your input, i shall give it a try once i have looked at otehr areas of my DB that i would like to finish.

Tonys solution has done the trick for me and because when i use my DB i need to ensure that i start with a fresh slate each time then deleting the table is what i wanted but was originaly doing it from a macro instead.

Thank you
Bev