Cheers Forny, long time no chat, I have not been on here for months due to working elsewhere, a friend used my log in to access here so that he could learn some access stuff, left a bloody mess!!
The code I used in the end to create an mdb & export the data I wanted (In case anyone else has the same problem!) was:
Sub CreateNewMDB(FileName, Format)
Dim Engine
Set Engine = CreateObject("DAO.DBEngine.36")
Engine.CreateDatabase FileName, ";LANGID=0x0409;CP=1252;COUNTRY=0", Format
End Sub
Private Sub exportplan_button_Click()
'declare the variables used for export.
Dim exporttable1, exportquery1, a, valueholder2
'On the form that you would run this code place a field for the user to name the file. (Me.exportfilename.Value)
'I have called the form output_export
' Populate the valueholder variable with the filename from form
valueholder2 = "C:\" & Me.exportfilename.Value & ".mdb"
'Check if the file exists
If Not (Len(Dir$(valueholder2)) <> 0) Then
'DAO 3.6 is required to run the code (You can also use other version of DAO, for example DAO.DBEngine.35)
Const dbVersion10 = 1
Const dbVersion11 = 8
Const dbVersion20 = 16
Const dbVersion30 = 32
Const dbVersion40 = 64
'Create Access2000 database
CreateNewMDB valueholder2, dbVersion40
'I have 13 export querys which obtain the data I want, I can then export the query into a table externally.
For a = 1 To 13
exportquery1 = "export_query" & a
exporttable1 = "ptw_export" & a
DoCmd.TransferDatabase acExport, "Microsoft Access", _
valueholder2, acTable, exportquery1, exporttable1
Next a
DoCmd.SetWarnings True ' Turn Application Warnings back on
'Notify the user of status
MsgBox "Data has been exported to " & valueholder2, _ vbInformation, "export wizard"
DoCmd.Close acForm, "output_plan_export" ' Close wizard form
Else ' The Database file already exists so please choose another name
MsgBox "The export file already exists, please choose another filename", vbCritical, "Error"
valueholder2 = "C:\" & Me.exportfilename.Value & "_2" & ".mdb"
End If
End Sub
