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

Hashiru

Active Member
Joined
May 29, 2011
Messages
286
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.

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:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
Hi Norie, I added the following lines of codes before the DoCmd.TrasnsferText, but access gave the same error msg.

Code:
If dbName ="" Then Exit Sub

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

Thanks.
 
Last edited:
Upvote 0
Hi All,
I have also used the following line but gives me the same error.


Code:
    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
 
Last edited:
Upvote 0
Why are you creating a new database from an existing one?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
If this code is in Excel where's the reference to the instance of Access you are automating?
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top