Access macro/VBA help 1

[JasonPurdueEE]

ok, heres a complicated one for you guys. I've busted my butt trying to figure this out on my own, but I'm not getting too far. any help you could give is greatly apprieciated. I'm trying to create a macro to automate a series of long and teadious tasks. this is what I need to do:

1. start out in an Access database and run a query. save as/export this query as an .xls file and close the query. this part I have pretty much down.

2. now for the hard part. have the macro open another database titled "jobs.mdb" and create a new table and choose the 'import a table' option. the table needed to be imported is of the 'Microsoft FoxPro' variety and is called "workpack." after it imports, I need it to open in design view and add a field called "PWR" with the data type text and add another field called "Phase" as the data type numbers. and I need to change a data type in the existing field called "WPNUMB" to numbers, and save the whole thing. whew, you guys still with me on this one? let me know if you need clarification.

3. here I need to do a whole lot of other stuff, but I can figure most of this out (I hope ).

and one last question: is there a 'record new macro' function in Access like there is in Excel? it would sure make some if this easier. again, any help would be great. thanks in advance.

 

[oharab]

I'll answer question 3 first, cos it's the easiest: No!
Question 2 I'll have a go at:

NOTE: This is aircode and will probably need tweaking to suit you exactly, but I hope the ideas will get you going. I use access 97, so you will need to make sure you have the DAO reference set if you are using 2k or above.

Dim dbJob As DAO.Database
Dim qd As DAO.QueryDef, strSQL As String
Dim td As DAO.TableDef, fld As DAO.Field

Set dbJob = OpenDatabase("C:\job.mdb&quot
Set td = dbJob.CreateTableDef("linkWorkPack&quot
td.Connect = "FoxPro 3.0;DATABASE=C:\FoxPro30\Samples"
td.SourceTableName = "WorkPack"
dbJob.TableDefs.Append td
Set td = Nothing

'table is now linked.
'will now create a make table query to create the new table.
strSQL = "SELECT linkWorkPack.* INTO WorkPack FROM linkWorkPack;"
dbJob.Execute strSQL
'remove the linked table, it's job is done.
DoCmd.DeleteObject acTable, "linkWorkPack"
Set td = dbJob.TableDefs("WorkPack&quot
'add new field PWR
Set fld = td.CreateField("PWR", dbText, 255)
td.Fields.Append fld
'add new field Phase
Set fld = td.CreateField("Phase", dbInteger)
td.Fields.Append fld
'change field WPNUMB
Set fld = td.Fields("WPNUMB&quot
fld.Type = dbInteger
dbJob.TableDefs.Refresh
dbJob.Close
Set td = Nothing
Set fld = Nothing
Set dbJob = Nothing

I don't use foxpro, so you may need to change the connect statement.
Let me know how you get on with it.

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[JasonPurdueEE]

Ben, thank you for your help. I tweaked your code and got it all to work except the change to the data type of WPNUMB. Can anybody think of another way to change the data type of an existing field?

 

[JasonPurdueEE]

nevermind. the field in question is not affected by the data type change in the end. thanks again.