Vba ошибка 3001

  • Remove From My Forums

locked

Access Runtime Error 3001 invalid argument — doing a Text Import

  • Question

  • I do a Call from a procedure a Sub. Getting an error message “Runtime Error 3001 invalid argument” Pointing to this specific rows:

            ‘Import the data from sPath with the specification

           
    DoCmd.TransferText acImportDelim, «AA1», «3PGodsmottagning», _

                «C:3Pimp» & sPath, True                     

    ‘ AA1 is the Text Import Specification ‘3PGodsmottagning is the table

    In my own debugging I tried to change the name of the Text Import Specification to a non-existing one “TEST” Then I got another error message, and a understandable one
    J Runtime Error “3625” – the Textfile specification doesn’t exist. And I’m fine with that. It was just a test.

    I have used the Microsoft Methods to create the Specification:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;2581495

     I have also tried to skip the Specification name as its Optional

    SpecificationName

    Optional

    Variant

    A string expression that’s the name of an import or export specification you’ve created and saved in the current
    database. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. To create a schema file, you can use the text import/export wizard
    to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.

      ‘Import the data from sPath with the specification        

    DoCmd.TransferText acImportDelim,  «», «3PGodsmottagning», _

                «C:3Pimp» & sPath, True

    Running this gave me another Error message “Runtime Error 3270” property not found.

    Obviously something or me is wrong. But what?

    Best Peter Forss

    • Edited by

      Friday, May 31, 2013 6:20 AM

Answers

  • Hurra!

    I found it! 

    I made a new Import Specification. This time I did it without saying «first line contains fieldnames»!!

    ‘Import the data from sPath with the specification
            DoCmd.TransferText acImportDelim, «Godsmottagning_108», «3P Godsmottagning», _
                «C:3Pimp» & sPath, True

    The HasFieldNames = «True» in the code line above does that part.

    Now it runs exactly as I like it to.
    Thanks Henry for supporting me.

    The new code is:

    Sub DailyImport3P()
    Dim rs As Recordset
    Dim sql As String
    Dim db As Database
    Set db = CurrentDb()

    Dim sPath As String
    sPath = Dir$(«C:3Pimp» & «*.txt», vbNormal)
    Debug.Print sPath
    Do Until sPath = «»
        sql = «SELECT * From 3PimporteradefilerGodsmottagning Where FileName='» & sPath & «‘»
        Set rs = CurrentDb.OpenRecordset(sql)

        
        ‘Check if the file name existing or not
        If rs.RecordCount = 0 Then

        
            ‘Import the data from sPath with the specification
            DoCmd.TransferText acImportDelim, «Godsmottagning_108», «3P Godsmottagning», _
                «C:3Pimp» & sPath, True
                Debug.Print sPath

            
            ‘Insert Filename not table
            sql = «Insert Into 3PimporteradefilerGodsmottagning (FileName) Values(‘» & sPath & «‘)»
            CurrentDb.Execute sql
            rs.Close
            Set rs = Nothing
            Set db = Nothing
        End If
        sPath = Dir$
    Loop

    End Sub

    Best Peter Forss

    • Marked as answer by
      ForssPeterNova
      Wednesday, June 5, 2013 6:24 PM

Источник

I am trying to run a query on a database server (Oracle 10g) using VBA/Excel and to display the results in a excel table. I have created the ODBC connection (read-only) and tested the connection. I works fine if I import data as a data source in excel (I am able to filter through a query as well) However my VBA code is giving me Error 3001

Sub Test()

Dim cnn As ADODB.Connection
Dim canConnect As Boolean
Dim rs As Recordset
Dim strSQL As String

Set cnn = New ADODB.Connection
cnn.Open "DSN=blah;Uid=blah;Pwd=blah"

strSQL = "select job_start_dttm, job_end_dttm from c_job"
Set rs = cnn.openrecordset(strSQL)
ActiveCell = rs(0)

End Sub

I get Error 3001 — Arguemnts are of worng type, are out of acceptable range, or are in confilct with one another

The query itself runs fine in SQL developer. Thanks

edit: The error is on «Set rs = cnn.openrecordset(strSQL)» line

asked Feb 16, 2011 at 2:15

Witcher's user avatar

WitcherWitcher

691 gold badge4 silver badges10 bronze badges

2

Try:

Sub Test()
Dim cnn As New ADODB.Connection
Dim canConnect As Boolean
Dim rs As New ADODB.Recordset
Dim strSQL As String

cnn.Open "DSN=blah;Uid=blah;Pwd=blah"

strSQL = "select job_start_dttm, job_end_dttm from c_job"
rs.Open strSQL, cnn
ActiveCell = rs(0)

End Sub

You seem to be mixing up a little DAO with your ADODB. You could have used Execute, but the above should suit.

answered Feb 16, 2011 at 9:01

Fionnuala's user avatar

FionnualaFionnuala

90.2k7 gold badges113 silver badges152 bronze badges

2

Try qualifying the type name of rs with the ADODB prefix to make sure it is not being defined as the built-in Access Recordset object type instead.

Dim rs As ADODB.Recordset

Edit:

You will also need to use the ADO .Execute command instead of the DAO .OpenRecordset:

Set rs = cnn.Execute("...")

answered Feb 16, 2011 at 3:18

mellamokb's user avatar

mellamokbmellamokb

56k12 gold badges109 silver badges136 bronze badges

1

Источник

I am trying to update table row with inputs from userform. below listed is the code i am trying to execute but its failing with msg «arguments are of the wrong type are out of acceptable range or are in conflict with one another vba» I checked the data type and all. If i use .execute method, its working but not like this.

I have similar code where i am fetching the row and updating back in same manner. only one field is updated in it though but its working fine.

What could be the reason gurus??

enter image description here
enter image description here

asked Nov 1, 2018 at 5:30

Vimal's user avatar

Pass true Date values to your date fields:

rst.Fields("MATURITY_DATE").Value = DateValue(Maturitydate.Value)
' …
rst.Fields("LAST_UPDATE_TIMESTAMP").Value = Now()

answered Nov 1, 2018 at 7:21

Gustav's user avatar

GustavGustav

53k7 gold badges29 silver badges55 bronze badges

8

Источник
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

Sub test06()
sSql = "DELETE BTI_TABLE"
Set RS = GetRs(sSql, cn)
 
'sSql = "create table BTI_TABLE (cex varchar2(10), status varchar2(30), sign varchar(255))"
'Set RS = GetRs(sSql, cn)
For i = 4 To 24999
 MRESULT = "XXX"
'If (Cells(i, 26) = "Нормирован") Or (Cells(i, 26) = "Согл") Or (Cells(i, 26) = "Утв") Or (Cells(i, 26) = "ЭЖН утв") Or (Cells(i, 26) = "ЭЖН норм") Or (Cells(i, 26) = "ЭЖН согл") Or (Cells(i, 26) = "ЭЖН утв") Then
Cells(i, 2).Select
txt = ActiveCell.FormulaR1C1
Cells(i, 33).Select
txt2 = ActiveCell.FormulaR1C1
For n = 1 To Len(txt2)
If Left(txt, n) = Left(txt2, n) Then MRESULT = Left(txt2, n)
 
Next n
Cells(i, 25).Select
txt = ActiveCell.FormulaR1C1
Cells(i, 5).Select
txt2 = ActiveCell.FormulaR1C1
sSql = "insert into BTI_TABLE values ('" + txt + "','" + txt2 + "','" + MRESULT + "')"
Set RS = GetRs(sSql, cn)
'End If
Next i
   
    'Cells(1, 1).Select
    'ЦЕХ 3
    sSql = "select count(distinct unvcode) from gate_perun.vw_plan_for_sdi where cexnum = 3"
Set RS = GetRs(sSql, cn)
Cells(2, 2).CopyFromRecordset RS
    sSql = "(select count(*) from BTI_TABLE b, gate_omp.konstrobj_today k, (select distinct unvcode from gate_perun.vw_plan_for_sdi) l where b.status = 'Согл'  and k.SIGN = rtrim(b.sign) and l.UNVCODE = k.UNVCODE and b.cex = 3)"
Set RS = GetRs(sSql, cn)
Cells(2, 3).CopyFromRecordset RS
 sSql = "(select count(*) from BTI_TABLE b, gate_omp.konstrobj_today k, (select distinct unvcode from gate_perun.vw_plan_for_sdi) l where b.status = 'Утв'  and k.SIGN = rtrim(b.sign) and l.UNVCODE = k.UNVCODE and b.cex = 3)"
Set RS = GetRs(sSql, cn)
Cells(2, 4).CopyFromRecordset RS
 sSql = "(select count(*) from BTI_TABLE b, gate_omp.konstrobj_today k, (select distinct unvcode from gate_perun.vw_plan_for_sdi) l where b.status = 'Нормирован'  and k.SIGN = rtrim(b.sign) and l.UNVCODE = k.UNVCODE and b.cex = 3)"
Set RS = GetRs(sSql, cn)
Cells(2, 5).CopyFromRecordset RS
 sSql = "(select count(*) from BTI_TABLE b, gate_omp.konstrobj_today k, (select distinct unvcode from gate_perun.vw_plan_for_sdi) l where b.status = 'ЭЖН согл'  and k.SIGN = rtrim(b.sign) and l.UNVCODE = k.UNVCODE and b.cex = 3)"
Set RS = GetRs(sSql, cn)
Cells(2, 6).CopyFromRecordset RS
  sSql = "(select count(*) from BTI_TABLE b, gate_omp.konstrobj_today k, (select distinct unvcode from gate_perun.vw_plan_for_sdi) l where b.status = 'ЭЖН утв'  and k.SIGN = rtrim(b.sign) and l.UNVCODE = k.UNVCODE and b.cex = 3)"
Set RS = GetRs(sSql, cn)
Cells(2, 7).CopyFromRecordset RS
 sSql = "(select count(*) from BTI_TABLE b, gate_omp.konstrobj_today k, (select distinct unvcode from gate_perun.vw_plan_for_sdi) l where b.status = 'ЭЖН норм'  and k.SIGN = rtrim(b.sign) and l.UNVCODE = k.UNVCODE and b.cex = 3)"
Set RS = GetRs(sSql, cn)
Cells(2, 8).CopyFromRecordset RS
       
    'sSql = "select * from BTI_TABLE" + ActiveCell.FormulaR1C1 + "END"
    'Set RS = GetRs(sSql, cn)
    'Cells(i, 3).CopyFromRecordset RS
   
'sSql = "drop table BTI_TABLE"
'Set RS = GetRs(sSql, cn)
 
tst = "D:ПРОИЗВОДСТВЕННЫЙ ПЛАН 2016"
ChDir tst
 
'================================
Workbooks.Open Filename:=tst + "цех 3.xlsx"
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Oracle Provider for OLE DB;Data Source=SDI;Password=ru;User ID=reader"
For i = 2 To 9999
 
 
Cells(i, 8).Select
sSql = "Select STATUS from BTI_TABLE where cex = 3 and sign = '" + ActiveCell.FormulaR1C1 + "'"
Set RS = GetRs(sSql, cn)
Cells(i, 10).CopyFromRecordset RS
 
Next i
Cells(1, 1).Select
 
End Sub
 
 
Function GetRs(sstr, cn)
Set rstdata = CreateObject("ADODB.Recordset")
    rstdata.Open sstr, cn
   
    Set GetRs = rstdata
 
Set rstdata = Nothing
 
End Function
Источник
title ms.prod ms.assetid ms.date ms.localizationpriority

Invalid argument. (Error 3001)

access

fa00c572-bf1b-4bf6-b482-837669081382

06/08/2019

medium

Invalid argument. (Error 3001)

Applies to: Access 2013 | Access 2016

You tried to perform an operation that involves a routine in a DLL, and one of the arguments to the routine is invalid. Check your entry to make sure you have specified the correct arguments, and then try the operation again.

This error also occurs when you attempt to use mutually exclusive constants in an argument to a method, such as specifying both dbConsistent and dbInconsistent in the options argument to the OpenRecordset method.

See also

  • Access for developers forum
  • Access help on support.office.com
  • Access help on answers.microsoft.com
  • Access forums on UtterAccess
  • Access developer and VBA programming help center (FMS)
  • Access posts on StackOverflow

[!includeSupport and feedback]

Источник

Понравилась статья? Поделить с друзьями:
  • Vba ошибка 1004 при открытии файла
  • Vba отключить проверку ошибок
  • Vba обработчик ошибок on error
  • Vba обработка ошибок err
  • Vba номер строки ошибки