Hi all my code hung up at the highlighted line (last line of code).
Please can anyone advice of what to do to resolve this problem? Thanks in advance.
Please can anyone advice of what to do to resolve this problem? Thanks in advance.
Rich (BB code):
Sub CreateNewDB_DAO()
Dim db As DAO.Database
Dim dbName As String
Dim tblNew As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
MyName = InputBox("Enter your chosen name for the database")
dbName = "C:\Users\C033732\Desktop\Dennis Weekly Report\" & MyName & ".accdb"
'dbName = "C:\Users\hashi\Desktop\PT\" & MyName & ".accdb"
'On Error GoTo ErrorHandler
Set db = CreateDatabase(dbName, dbLangGeneral)
Set tblNew = db.CreateTableDef("CurrentData")
'Create Fields
'Dim NewSht As Worksheet
'Set NewSht = ThisWorkbook.Worksheets("Fields")
'LastColumn = NewSht.Cells(1, Columns.Count).End(xlToLeft).Column
'For i = 1 To LastColumn
'Set fld = tblNew.CreateField(NewSht.Cells(1, i))
'tblNew.Fields.Append fld
'Next i
'db.TableDefs.Append tblNew
'Create Fields
Set fld = tblNew.CreateField("FAIN", dbText)
tblNew.Fields.Append fld
Set fld = tblNew.CreateField("Fund", dbText)
tblNew.Fields.Append fld
Set fld = tblNew.CreateField("Scope", dbText)
tblNew.Fields.Append fld
Set fld = tblNew.CreateField("ALI", dbText)
tblNew.Fields.Append fld
Set fld = tblNew.CreateField("Project", dbText)
tblNew.Fields.Append fld
Set fld = tblNew.CreateField("BRD Amount", dbCurrency)
tblNew.Fields.Append fld
Set fld = tblNew.CreateField("RMB Amount", dbCurrency)
tblNew.Fields.Append fld
Set fld = tblNew.CreateField("UTL Amount", dbCurrency)
tblNew.Fields.Append fld
Set fld = tblNew.CreateField("Type", dbText)
tblNew.Fields.Append fld
Set fld = tblNew.CreateField("Package", dbText)
tblNew.Fields.Append fld
db.TableDefs.Append tblNew
'import CSV into temp table
'Browse for the Datasource and set the title of the dialog box.
Dim MyFile As FileDialog
Set MyFile = Application.FileDialog(msoFileDialogFilePicker)
With MyFile
.Title = "Browse for the Text File (file extension is .txt)"
If .Show = True Then
' Assign the file to a variable Reportbk.
txtfilepath = MyFile.SelectedItems.Item(1)
Else
MsgBox "You clicked Canncel in the file dialog box.", , "Canceling the import process"
Exit Sub
End If
End With
DoCmd.TransferText TransferType:=acImportDelim, TableName:=tblNew.Name, Filename:=txtfilepath, HasFieldNames:=True
End Sub
Last edited: