Dummy VB trying DoCmd.Transfer Databse (1 Viewer)

[Rich_Lovina]

Okay...in the past I used a Macro for this action. Now I want to learn to do it in Code.

I've weekly data coming in...there are 25 tables in the Access db, I want to select 5 of the 25 same named tables each week; just the .mdb name changes. My queries are:

1. Where do I put the code? In a command button on my MainInputForm? or some other button on my main object screen?
2. How do I get a message to popup allowing me to change the pathname each week?
3. each of the 5 tables have varying fldnames which I append together (Can I code these append queries so they all go into the one table called TRANSMAST?)

Ive done my homework in the Forums searchengine, but nothing covers this exact situation.
Thanks in advance any guidance.

 

[BukHix]

Okay...in the past I used a Macro for this action. Now I want to learn to do it in Code.

The easiest way to convert macros into code is to right click on the macro and choose SaveAs/Export and then choose Save as Visual Basic Module. Uncheck the next two boxes (optional) and then your commands can be cut and pasted from the module right into the event of your choice.

Keep in mind that the conversion does not always creates the most efficient code but it usually will work without much modification.

1. Where do I put the code? In a command button on my MainInputForm? or some other button on my main object screen?

You can put it in any event that makes sense for your application. Right click on any object and choose properties. Next select the event tab, from there you need to decide the best event for your trigger. For a command button you would choose the OnClick event. Put your cursor in the textbox to the right of the event and click the ellipses that will appear at the right. Choose code builder from there and you can build or paste your code here.

2. How do I get a message to popup allowing me to change the pathname each week?

You can use an input box to create the variables that will be used for the name each week. Here is an example of some code that does something similar to what you are trying to do.

' Get variable to hold date
    Dim dtDate As Date

' create variable for file name
    Dim strName As String
    Dim cancel As Integer

' Get Current date to use in file name
    dtDate = Date

' Put variables together to form the file name
    strName = "FormD" & Format(dtDate, "mmdd")

' Turn the warnings off
    DoCmd.SetWarnings False

' Run some queries to manage data
    DoCmd.OpenQuery "FormDFilter", acNormal, acEdit
    DoCmd.OpenReport "FormDReport1", acPreview

' Transfer the new file to another database
    DoCmd.TransferDatabase acExport, "Microsoft Access", _
        "D:\DataPath\BackUp.mdb", acTable, _
            "FormD", strName, False
' More query stuff
    DoCmd.OpenQuery "FormDClear", acNormal, acEdit
    DoCmd.SetWarnings True

HTH post back if you have more questions.

 

[Rich_Lovina]

Thanks greatly BukHix. This gives me something to get my teeth into.

 

[BukHix]

Rich I forgot to add an imput box to the code I posted above. You could do it something like this:

Dim strFileName As String
Dim strName As String

strFileName = InputBox("Enter a file name")
strName = "FileName" & strFileName

 

[Rich_Lovina]

Took me a while to get back on this one Buk, but I'm still not sure whether yr code actually opens an Input box or do I have to design/make one.
Then having made inputbox how do I pathname to it? Is it D:/MyData/Forms!MyInputbox or what?
Where does yr last code go relative to yr 1st bit of code?
Sorry, been learning Frontpage and forgotten some Access basics.