Run-Time Error '2046': The Command or action TransferText isn't available now.

[Hashiru]

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.

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

 

[Norie]

I don't have an appropriate text/CSV file to fully test the code with but I would have thought that if you were trying to import into a table in the newly created database then that database would need to be open.

 

[Hashiru]

Hi Norie, I added the following lines of codes before the DoCmd.TrasnsferText, but access gave the same error msg.

If dbName ="" Then Exit Sub

   If Dir(dbName="" Then
         MsgBox dbNam & " was not found."
         Exit Sub
   End If
Set db = OpenDatabase(dbName)

Thanks.

 

[Hashiru]

Hi All,
I have also used the following line but gives me the same error.

    DoCmd.RunSQL "SELECT FAIN, Fund, Scope, ALI, Project,  Type, Package INTO TargetTable FROM [Text;DATABASE=C:\Users\C033732\Desktop\Dennis Weekly Report;HDR=Yes].TestFile.csv"

May I be missing a reference to a library?
What should make the command RunSQL or TransferText available as it is indicating that it is not available as indicated by run time error 2046
Thanks

 

[Norie]

Why are you creating a new database from an existing one?

 

[Hashiru]

I was finding alternative to get pass this line of code, that is a stumbling block. Does this RunSQL not an equivalent in Access SQL for the TransferText?

 

[Hashiru]

Here are the steps in Access GUI that I want to achieve using VBA:

(1) Click External Data Tab
(2) Under Import & Link Group, Click Text File
(3) Browse for the file
I. Import the source data into a new table in the current database.
(4) Click OK
(5) Delimited - characters such as comma ab separate
(6) First Row Contains Field Names
(7) No Primary Key OK Finish

 

[xenou]

Is your code in an Access database? Also, what is the purpose of creating a new datatabase just to hold data from one CSV file?

 

[Hashiru]

I have to pull two sets of csv files into MS Access 2016 (into separate tables), create a relationship between two tables and build a query that will give me a list of Items that have changed from previous week. Each file contained records above 2 million currently and the weekly data is cumulative. currently the two files are 223,667 KB an 607,425 KB. I tried merge query in excel but I ran out of memory.

I have the code in Excel. I code in Excel so this journey is new to me ( I am a New Bee in Access Programming).

Note, the two csv files have the same field names. I actually found out that the RID field is missing which is what I what to use to relate the tables and calculate the change in RMB and query out those items that have changed (RID where the RMB has changed).

Thanks for all the right questions you asked.

 

[Norie]

If this code is in Excel where's the reference to the instance of Access you are automating?