need VB code to combine multiple access files into one

[vishalsalunkhe1]

Hi,
I want to combine multiple access (*.mdb) files into one mdb file. All the files have same table and same fields. The structure of target database will also be exactly same as source files. I am actually trying to create an exe file in VB6 which will have browse option to select source mdb files and browse for target mdb location. and when I click on OK button , it should consolidate all the data from source files into target file. I am not a programmer still i am able to create a dialog box but I can't write a code to combine mdb files, Could you please help me to achieve my goal? I need to complete it by thursday anyhow so your earlist help is appreciated.
CODE -
'Declare a variable as a FileDialog object.
Dim fd As Office.FileDialog
Dim app
Dim OutFilename As String
Dim InFolder As String

Private Sub BrowseInput_Click()

Set fd = app.FileDialog(msoFileDialogFolderPicker)

With fd

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then

InFolder = .SelectedItems(1)

InputPath.Text = InFolder

'The user pressed Cancel.
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing

End Sub
Private Sub OkButton_Click()

'Write Conversion Code here
Dim strPattern As String
Dim strFile As String
Dim currFile As String

strPattern = InFolder & "\" & "*.mdb"

strFile = Dir(strPattern, vbNormal)

Do While Len(strFile) > 0

currFile = InFolder & "\" & strFile

Debug.Print strFile

strFile = Dir
Loop

End
End Sub

Private Sub BrowseOutput_Click()

'Create a FileDialog object as a File Picker dialog box.
Set fd = app.FileDialog(msoFileDialogSaveAs)

With fd

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then

OutFilename = .SelectedItems(1)

OutputPath.Text = OutFilename

'The user pressed Cancel.
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing


End Sub

Private Sub CancelButton_Click()

End

End Sub

Private Sub Form_Load()
' Default property is assigned to Type 8 Variant RetVal.
Set app = CreateObject("Access.Application")
app.Visible = False

End Sub


:banghead:
:banghead:

 

[MarkK]

Your goal, to create a VB6 program to merge a bunch of Access databases, is beyond the scope of a single thread. Also, notice the octothorpe, the number sign "#" on the the toolbar above the post window. Use that to offset your code and preserve your indents, which make things much easier for your readers to understand. Here's an example . . .

Private Sub TestSub()
[COLOR="Green"]   'demonstrates how much more readable your code can look . . .[/COLOR]
[COLOR="Green"]   'declare a variable as a FileDialog object.[/COLOR]
   Dim fd As Office.FileDialog
   Dim app
   Dim OutFilename As String
   Dim InFolder As String

   Set fd = app.FileDialog(msoFileDialogFolderPicker)
   With fd
[COLOR="Green"]      'do something with fd[/COLOR]
   End With
End Sub

What I recommend is that you present more specific problems one at a time, with enough detail for someone to help troubleshoot. These posts with very large objectives are much harder to answer in a useful way.

One general consideration I would offer is consider writing your code in VBA, since it's native to Access. Everything you'll need to do to consolidate your data will be available to you in VBA, and it's simpler.

Hope this helps,

 

[vishalsalunkhe1]

Thanks for your reply. I am new to this site so i thought dictating complete problem would be more helpful. I will take care of it next time.
I can create it in VBA but it doesn't give me option to create exe file and I am supposed to deliver it in the form of tool which will be easily accessible for user.
In my code I doesn't understand where to read data from multiple files and how to write it in target file. I am even trying to write a query in access and run it as an argument but I don't have any success yet. If you could help me in any way i would really appreciate it. I am running short of time.

 

[MarkK]

Everybody is short of time.

You raise a number of issues. 1) "deliver it in the form of tool which will be easily accessible," 2) "where to read data from multiple files", 3) "how to write it in target file", 4) "write a query in access and run it as an argument"

Which one do you want to start with? How about 4?

Essentially you need to copy data from one table to another, correct? Show the table structure including field names. Show the query you tried. Describe how it failed, what the errors were, specifically, and we'll see if we can move something ahead.