Results 1 to 7 of 7
  1. #1
    New Lounger Caoimhin.Dev's Avatar
    Join Date
    Mar 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post Run-time error 3265 with ADO to Access 2013 .accdb file

    I'm trying to send some data to an Access 2013 .accdb database from a Word 2013 VBA module. What I get is:

    Runtime 3265.png

    My code is as follows:

    Code:
        Dim StrDBPath As String
        Dim StrDBName As String
        Dim MyAccess As Access.Application
        Dim ObjectDatabase As Object 
        Dim MyRecordSet As ADODB.Recordset
        Dim MyConnection As ADODB.Connection
        Dim MyCommand As ADODB.Command
    
        StrDBPath = "C:\Users\" & Environ("Username") & "\My Documents\Change-A-Roo\"
        StrDBName = "Change Management.accdb"
        Set MyAccess = GetObject(StrDBPath & StrDBName, "Access.Application")
        Set ObjectDatabase = MyAccess.CurrentDb
        Set MyCommand = New ADODB.Command
    
        MyCommand.CommandText = "Tickets"
        MyCommand.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
                                     "Dbq=" & StrDBPath & StrDBName & ";" & _
                                     "Uid=admin;" & _
                                     "Pwd=;"
        Set MyRecordSet = New ADODB.Recordset
        MyRecordSet.Open MyCommand, , adOpenDynamic, adLockPessimistic, adCmdTable
                               
        With MyRecordSet
            .AddNew
            .Fields("Title").Value = CStr(DateStart)  <--- I get the error at this point!!!
            .Fields("Work Date").Value = CDate(DateStart)
            .Fields("Status").Value = 1
            .Fields("File Path").Value = CStr(StrFilePath) & CStr(StrFileName) & ".html"
            .Update
            .Close
        End With
        
        Set MyRecordSet = Nothing
    I've tried altering the CommandText to a Select statement with both the fieldnames and the * wildcard. I've double checked the field types in the database:

    Ticket Table.png

    I have a primary key in the database. What am I doing wrong here?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,465
    Thanks
    3
    Thanked 128 Times in 121 Posts
    What is the value of the variable DateStart? What data type does it have?

    Your next line is using CDate to convert it to a date which implies it is not already a date.

    If it is a date, try CStr(#DateStart#)
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    218
    Thanks
    0
    Thanked 19 Times in 18 Posts
    Quote Originally Posted by Caoimhin.Dev View Post
    I'm trying to send some data to an Access 2013 .accdb database from a Word 2013 VBA module. What I get is:

    Runtime 3265.png

    My code is as follows:

    Code:
        Dim StrDBPath As String
        Dim StrDBName As String
        Dim MyAccess As Access.Application
        Dim ObjectDatabase As Object 
        Dim MyRecordSet As ADODB.Recordset
        Dim MyConnection As ADODB.Connection
        Dim MyCommand As ADODB.Command
    
        StrDBPath = "C:\Users\" & Environ("Username") & "\My Documents\Change-A-Roo\"
        StrDBName = "Change Management.accdb"
        Set MyAccess = GetObject(StrDBPath & StrDBName, "Access.Application")
        Set ObjectDatabase = MyAccess.CurrentDb
        Set MyCommand = New ADODB.Command
    
        MyCommand.CommandText = "Tickets"
        MyCommand.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
                                     "Dbq=" & StrDBPath & StrDBName & ";" & _
                                     "Uid=admin;" & _
                                     "Pwd=;"
        Set MyRecordSet = New ADODB.Recordset
        MyRecordSet.Open MyCommand, , adOpenDynamic, adLockPessimistic, adCmdTable
                               
        With MyRecordSet
            .AddNew
            .Fields("Title").Value = CStr(DateStart)  <--- I get the error at this point!!!
            .Fields("Work Date").Value = CDate(DateStart)
            .Fields("Status").Value = 1
            .Fields("File Path").Value = CStr(StrFilePath) & CStr(StrFileName) & ".html"
            .Update
            .Close
        End With
        
        Set MyRecordSet = Nothing
    I've tried altering the CommandText to a Select statement with both the fieldnames and the * wildcard. I've double checked the field types in the database:

    Ticket Table.png

    I have a primary key in the database. What am I doing wrong here?
    According to your Access screenshot, the field is called Titile, but in the code it is referred to as Title. I assume Title was your intention

  5. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,465
    Thanks
    3
    Thanked 128 Times in 121 Posts
    Nice pickup Jeremy
    According to your Access screenshot, the field is called Titile, but in the code it is referred to as Title. I assume Title was your intention
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #5
    New Lounger Caoimhin.Dev's Avatar
    Join Date
    Mar 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey Andrew,

    It's setup as a Date:

    Public DateStart As Date

    And it's calculated based on a If statement earlier in the script. I have the value of DateStart and the VarType being dumped in a message box before the script tries to open the database. I put the conversions in just for grins and giggles. I had seen on another form that the issue could be caused by field mismatches. Heck I even tried a static date and that still didn't work.
    Last edited by Caoimhin.Dev; 2014-03-17 at 10:06.

  7. #6
    New Lounger Caoimhin.Dev's Avatar
    Join Date
    Mar 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh wow. I never noticed that. OK now I feel stupid.

  8. #7
    New Lounger Caoimhin.Dev's Avatar
    Join Date
    Mar 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much for pointing that out. I guess I could see the Forrest for the Trees. It is working now as intended.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •