Ado recordset error

• 
• VBForums
• Visual Basic
• Visual Basic 6 and Earlier
• [RESOLVED] Error on ADO Recordset Update

1. Jan 19th, 2019, 12:45 AM

   
   

   #1

   

   Thread Starter

   
   Junior Member
   

   

   ---

   [RESOLVED] Error on ADO Recordset Update
   

   I am trying to import data from Excel to an Access table. When I import the data into a recordset using ADO, some of the fields come in a null due to the type of field Access thinks it is. I want to update the nulls in the recordset to «TBD». I can see the update to field occur while I watch the steps, but I get an error when it gets to the .update code. The error I receive at the .update line is «You cannot record your changes because a value you entered violates the settings defined for this table or list…».

   Can someone help me with my code so I can update some of the recordset fields? My current code is…

   Public Sub EditRS()

   Const fName As String = «C:Users40815409Desktop2018 Conduent SOCR WEEKLY Status Document 12172018.xlsx»
   Dim cnn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim rs As ADODB.Recordset
   Dim DraftReviewUpdate As Long
   Dim DraftReview As String
   Dim InterimStart As Long
   Dim InterimClose As Long
   Dim FinalStart As Long
   Dim FinalClose As Long
   Dim SQL As String
   Dim i As Integer

   ‘Open connection to our spreadsheet
   Set cnn = New ADODB.Connection
   With cnn
   .Provider = «Microsoft.ACE.OLEDB.12.0»
   .ConnectionString = «Data Source=» & fName & «;» & _
   «Extended Properties=»»Excel 12.0 Xml;HDR=Yes;»»;»

   .Open
   End With

   Set cmd = New ADODB.Command
   cmd.ActiveConnection = cnn
   cmd.CommandType = adCmdText
   Set rs = New ADODB.Recordset

   ‘ Query the workheet
   SQL = «SELECT AuditReportNumber, DraftReview FROM [2018Overview$] «

   cmd.CommandText = SQL
   rs.CursorLocation = adUseClient
   rs.CursorType = adOpenOptimistic
   rs.LockType = adLockOptimistic
   rs.Open cmd

   For i = 1 To rs.RecordCount — 1
   If IsNull(rs.Fields(«DraftReview»).Value) Then
   rs.Fields(«DraftReview»).Value = «10/10/2018»
   rs.Update
   Debug.Print rs.Fields(«DraftReview»)
   Else: Debug.Print rs.Fields(«DraftReview»)
   End If
   rs.MoveNext
   Next i

   rs.Close

   End Sub

   ---

2. Jan 19th, 2019, 01:11 AM

   
   

   #2

   Re: Error on ADO Recordset Update
   

   You say you want to update the field to ‘TBD’ but your code is updating it to a date value. Which one is giving you the error and what type of field is it in the access database? ‘TBD’ would only be allowed if it is a text field.

   Also it looks like your code is trying to update an excel spreadsheet and not an access database.

   ---

3. Jan 19th, 2019, 04:01 PM

   
   

   #3

   

   Thread Starter

   
   Junior Member
   

   

   ---

   Re: Error on ADO Recordset Update
   

   Sorry I should have changed the TBD to a date (is 10/14/18) before I posted. I think the recordset field type is a date field because the first 20 lines from the Excel spreadsheet are dates. I am trying to update the recordset value before I write them to the access table. When I read the values into the recordset some of the values are null. When I write them to the access table the null values don’t write to the table. I want to be able to capture the null values as something else when they move to the table. Hope this makes sense.

   ---

4. Jan 19th, 2019, 04:02 PM

   
   

   #4

   

   Thread Starter

   
   Junior Member
   

   

   ---

   Re: Error on ADO Recordset Update
   

   Sorry I should have changed the TBD to a date (is 10/14/18) before I posted. I think the recordset field type is a date field because the first 20 lines from the Excel spreadsheet are dates. I am trying to update the recordset value before I write them to the access table. When I read the values into the recordset some of the values are null. When I write them to the access table the null values don�t write to the table. I want to be able to capture the null values as something else when they move to the table. Hope this makes sense.

   ---

5. Jan 19th, 2019, 04:16 PM

   
   

   #5

   Re: Error on ADO Recordset Update
   

   If you change the null values to a valid default date, then you can check the values to see if they are the default date and change them back to Null if needed. But the question needs to be asked, why not just leave them as null?

   Tip: to know for sure what data type is being used when you get the recordset from Excel, simply print out: .Field(«FieldName»).Type. That numeric value can be looked up in the DataTypeEnum list to see what its human-readable type is, i.e., a value of 7 = adDate

   ---

6. Jan 19th, 2019, 06:44 PM

   
   

   #6

   

   Thread Starter

   
   Junior Member
   

   

   ---

   Re: Error on ADO Recordset Update
   

   Thank you LaVolpe. Figuring out the field type of the recordset is something I couldn’t previously figure out how to do. Maybe I can explain a little better what I am trying to do and what the data I am trying to import is. The Excel column I am importing is primarily date entries in the form or 1/1/2019, but it also has some entries of «TBD» in the case the date isn’t known yet. When I bring this data into the recordset, it changes the TBD values to Null, then it dumps the Null value and leaves a blank when I post it to a table. So here are the questions…

   1. Is there anyway to read all of the Excel column values in as they are regardless of them being a date or some other text (ie TBD)? I have searched on this and don’t think its possible as Access automatically determines the recordset field type based on the first x rows of data and you can’t set the recordset field type manually. In this case the first 20 rows have an actual date before it gets to any fields with TBD.

   2. If 1 is not possible, is there anyway I can change the recordsets null values to TBD when I update them in the Access table? Or preserve the null value when I update the table so I can change them through a query after the table is populated?

   Again, thank you for any help on this.

   ---

7. Jan 19th, 2019, 06:47 PM

   
   

   #7

   

   Thread Starter

   
   Junior Member
   

   

   ---

   Re: Error on ADO Recordset Update
   

   Also…the field type of the recordset was 7…adDate.

   ---

8. Jan 19th, 2019, 06:53 PM

   
   

   #8

   Re: Error on ADO Recordset Update
   

   Try appending this to end of your extended properties in the connection string: IMEX=1
   i.e., «Extended Properties=»»Excel 12.0 Xml;HDR=Yes;IMEX=1;»»;»

   That property is suppose to indicate «mixed data types». That might solve your problem. If so, then is your Access DB field specified as text? If not, that will be a different problem when you try to import TBD into a date database field.

   ---

9. Jan 19th, 2019, 07:12 PM

   
   

   #9

   

   Thread Starter

   
   Junior Member
   

   

   ---

   Re: Error on ADO Recordset Update
   

   My data type of the Access table field is LongText, so that should be fine. I added IMEX=1 to my extended properties and that didn’t make a difference. Here is the immediate window result…

   10/5/2018
   10/10/2018
   Null
   Null
   8/24/2018
   9/26/2018
   9/20/2018
   11/14/2018
   Null
   11/14/2018
   1/7/2019
   11/9/2018

   Any other thoughts?

   ---

10. Jan 19th, 2019, 07:19 PM

    
    

    #10

    Re: Error on ADO Recordset Update
    

    Other thoughts? Thinking 2 possibilities, but I’d wait for some other die-hard Excel gurus to reply…

    1) Sort the Excel column to ensure all date values are towards bottom & TBD near top
    2) Say the Excel sheet has no headers (HDR=No) and that could force all columns to be read as text (assuming the column headers are text-like) but has at least 2 major pitfalls

    a. can’t use field names in queries, must use names like F1, F2, etc
    b. will treat all columns/fields as text even if you don’t want them to be treated as such

    ---

11. Jan 19th, 2019, 07:25 PM

    
    

    #11

    

    Thread Starter

    
    Junior Member
    

    

    ---

    Re: Error on ADO Recordset Update
    

    My data type of the Access table field is LongText, so that should be fine. I added IMEX=1 to my extended properties and that didn’t make a difference. Here is the immediate window result…

    10/5/2018
    10/10/2018
    Null
    Null
    8/24/2018
    9/26/2018
    9/20/2018
    11/14/2018
    Null
    11/14/2018
    1/7/2019
    11/9/2018

    Any other thoughts?

    ---

12. Jan 19th, 2019, 07:37 PM

    
    

    #12

    

    Thread Starter

    
    Junior Member
    

    

    ---

    Re: Error on ADO Recordset Update
    

    Resorting the Excel column did the trick. Now I will need to figure out how to sort that column in the VBA code before selecting it for the recordset. Thank you for the input.

    Also, sorry for the duplicate posts. I’m not sure why it is doing that.

    ---

13. Jan 19th, 2019, 07:47 PM

    
    

    #13

    Re: Error on ADO Recordset Update
    

    For more info, maybe this link might be helpful?

    ---

14. Jan 19th, 2019, 08:17 PM

    
    

    #14

    Re: Error on ADO Recordset Update
    

    Why not just read the excel data into an array, sort the array using a Bubble Sort, and THEN updating your database table…

    sorta like this (without the BSort):

    Code:

    ...
    cmd.CommandText = SQL    
        rs.CursorLocation = adUseClient
        rs.CursorType = adOpenDynamic
        rs.LockType = adLockOptimistic
        rs.Open cmd
        Dim dateArray() As Date
        ReDim dateArray(rs.RecordCount) As Date
        For i = 1 To rs.RecordCount - 1
            If Not IsDate(rs.Fields("mdate").Value) Then
                dateArray(i) = CDate("10/10/2018")
                rs.Update
            Else:
                dateArray(i) = rs.Fields("mdate")
            End If
            rs.MoveNext
            Debug.Print dateArray(i)
        Next i
        rs.Close

    ---

15. Jan 19th, 2019, 08:21 PM

    
    

    #15

    Re: Error on ADO Recordset Update
    

    @Sam. I think I fully understand his problem

    The Excel column is filled with date formatting with other values having the string: TBD. Whether there are blank values or not, don’t know.

    Well, currently the 1st 8+ values in that column are date strings or blank, none have the word TBD until later down the column. Therefore, Excel is populating his recordset with that field as a Date (data type = adDate). And any values that cannot be converted to a date, i.e., the word TBD, are returned as NULL. Therefore, don’t see how sorting can help

    after the recordset is populated by Excel

    ---

16. Jan 19th, 2019, 08:46 PM

    
    

    #16

    Re: Error on ADO Recordset Update
    

    Yeah….I was about to comment on my comment….really no need to SORT to update or insert into a table anyway. RIGHT????
    But, now that you have explained HIS problem a bit more, I still see that my ‘approach’ would/could work. That is, first check if the value is a date (IsDate()), then if he wants to have JUST THE TBD’s to be changed to a date (and not the other non-date ones), then IF it…but still simply put what he wants into an array. Of course, what he is explaining/attempting is to change the Excel fields first, then move to databases. I don’t see a need for that. If he wants the excel to mirror the db, then once the table is populated, he exports it back out (as you know, very simple with VB code) to replace the original data that has the TBD’s and/or blank fields. Then, once there, update (insert/change/even delete) the data from the array into his table…am I missing even more? But again, to update a TBD to a fixed date (or even a blank date to that) is not logical. I would instead, put SOMETHING into the database, like 1/1/1900 for example, if he NEEDS all the rows in the db to have dates in them, knowing that that fixed date is a placeholder…his subsequent VB code when retrieving from the DB simply needs to know of that placeholder.

    ---

17. Jan 19th, 2019, 08:50 PM

    
    

    #17

    Re: Error on ADO Recordset Update
    

    I got a little verbose/confusing when I last posted, but I think you, LaVolpe, understand what I’m attempting to say.

    ---

18. Jan 19th, 2019, 08:56 PM

    
    

    #18

    Re: Error on ADO Recordset Update
    

    Here’s my tiny example without inserting/updating an Access Database, just reading the excel column and putting the data in an array, replacing non-date entries with 1/1/1900.Attachment 164879

    ---

19. Jan 19th, 2019, 10:22 PM

    
    

    #19

    

    Thread Starter

    
    Junior Member
    

    

    ---

    Re: Error on ADO Recordset Update
    

    Thank you both for the comments and suggestions. However, I am not trying to replace the TBD values with a date. The issue is when I bring the date into Access, access reads the first 8 rows and decides it is adDate type because they are all dates. Further down the Excel column it reads a «TBD» value and brings that into Access as a Null because the recordset field type has already been set to adDate. I don’t know much about the array, but if you specify the array as a date, I would imagine I’d run into the same issue as I am now. Sorting the Excel column so the non-date values are at the top fixes the problem, now I need to see if there is a way to sort the specific Excel column using VBA before I query the data into the recordset.

    ---

20. Jan 20th, 2019, 01:10 AM

    
    

    #20

    Re: Error on ADO Recordset Update
    

    It sounds like the real problem is that you are using a function that allows Access to try and automagically create the table.
    A better approach would be to create the table yourself and then import the data into the table. This would give you full control over what is imported and how.

    ---

21. Jan 20th, 2019, 02:42 AM

    
    

    #21

    Re: Error on ADO Recordset Update
    

    Originally Posted by troybos
    

    The issue is when I bring the date into Access, access reads the first 8 rows and decides it is adDate type because they are all dates. Further down the Excel column it reads a «TBD» value and brings that into Access as a Null because the recordset field type has already been set to adDate.

    You need *all* the fields to be Text to enter «TBD» as string. Here is where IMEX mode comes into play with following simple strategy:
    1. Use IMEX=1
    2. Set TypeGuessRows=1 (in registry for Microsoft.ACE.OLEDB.12.0, in connect string for Microsoft.Jet.OLEDB.4.0)
    3. Use HDR=0

    Last one forces the provider to look at the *column names* only (first 1 row) and to conclude that the format of every column is Text. . . LOL

    cheers,
    </wqw>

    ---

22. Jan 20th, 2019, 02:53 AM

    
    

    #22

    Re: Error on ADO Recordset Update
    

    Hi,

    another way for Test is to Impost the whole Excel sheet to a new Access Table
    and do the sorting there

    adoConnection is my valid connection to the DB

    Code:

    Dim SSql As String
    
    'Import from Excel to new Table in Access
     SSql = "SELECT * INTO TabExeclD From [Tabelle1$] In " & _
                                  "'E:Book1.xls' 'EXCEL 8.0;' "
    adoConnection.Execute SSql

    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 — 3900 were counted. with Baby Callas it’s 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

    ---

23. Jan 20th, 2019, 06:35 AM

    
    

    #23

    Re: Error on ADO Recordset Update
    

    @troybos…did you look at that project I attached…it shows how to create, re-dimension it based up number of records, then how to go through it (the debug statement). Notice my array is of Date type, so when I got to a TBD string in your Excel Column, I simply inserted a date instead. So, once you have your array (which has a date placeholder (like 1/1/1900 in my example), you simply go through that array element by element and insert the information into an Access Table. I can gin an example using that little project if you really need it. But, it is very simple to do.

    ---

24. Jan 20th, 2019, 07:41 AM

    
    

    #24

    Re: Error on ADO Recordset Update
    

    like others, i get bored in my old age as well…so here is my revised example…taking data from an excel spreadsheet with dates and TBD’s in a column and inserting the dates as well as a placeholder date (1/1/1900) into an access (2007) database table.

    ---

25. Jan 20th, 2019, 12:34 PM

    
    

    #25

    

    Thread Starter

    
    Junior Member
    

    

    ---

    Re: Error on ADO Recordset Update
    

    @wqweto…for #2 I am using Mircosoft.ACE.OLEDB.12.0. Is it the Windows registry this entry needs to go into? Can you tell me which registry key?

    I put in the IMEX and HDR value…»Extended Properties=»»Excel 12.0 Xml;HDR=0;IMEX=1″»;»

    @SamOscarBrown…I’m not trying to replace the TBD with dates. I’m trying to preserve the TBD’s as I bring them into my table from Excel. The issue is that without sorting the column correctly in Excel, the TBD’s come into the Access recordset as Null and those Null values get turned to blanks when I write them to my table, so I completely lose which entries should have stayed as TBD.

    The sorting of the Excel file to put the TBD fields at the top and the dates at the bottom works all the way through to the table. But now I can’t find the right way to sort the Excel column through vba.

    ---

26. Jan 20th, 2019, 01:02 PM

    
    

    #26

    Re: Error on ADO Recordset Update
    

    1-you don’t need to sort things before putting them into a table.
    2-The example I provided used a DATE field in my Access table. If I change that field to a TEXT field, I can simply move all records («TBD» and ‘dates’ as strings).
    3-What is the purpose of sorting the Excel column if you put the data into an Access table?

    See revised example.

    Attachment 164893

    ---

27. Jan 20th, 2019, 01:13 PM

    
    

    #27

    Re: Error on ADO Recordset Update
    

    Originally Posted by SamOscarBrown
    

    3-What is the purpose of sorting the Excel column if you put the data into an Access table?

    Sam, let me try to explain… The overall problem is due to Excel’s method of guessing what type of data exists in a column based on like the first 8 field values.

    Sorting guarantees that the Excel column looks something like this:

    TBD
    TBD
    DateValue
    DateValue

    vs something like this:

    DateValue
    7+ more DateValues
    TBD
    TBD

    Now in that 2nd sample, the recordset will convert TBD to NULLs and sets the RS field type to Dates. This prevents knowing the original value. I am assuming that valid NULLs also exist in the recordset, otherwise, the simple solution is to treat all NULLs as TBD.

    In the first example, the recordset contains both TBD and date values (as strings/text); the RS field type is text. This is what the OP wants

    ---

28. Jan 20th, 2019, 01:26 PM

    
    

    #28

    Re: Error on ADO Recordset Update
    

    Aye…got all that. But what confuses me most is WHY? Why worry about how the Excel data is sorted if OP is going to import that stuff into an Access Database? My 3rd example treats EVERYTHING as Strings. Once they are in a Text Field in an Access database (not concerned with ORDER), it is simple to convert the ‘strings that look like dates’ back to dates, and ignore the TBD’s.

    So, if I am way off base with MY logic….I’m outa here and let you teach…Sammi

    ---

29. Jan 20th, 2019, 01:27 PM

    
    

    #29

    

    Thread Starter

    
    Junior Member
    

    

    ---

    Re: Error on ADO Recordset Update
    

    When I open your DB I only see the table. Is there any code that shows how you did this?

    Maybe I haven’t explained the problem well enough. Let me try again with some of my real data.

    When I run this code:

    Code:

    Public Sub PrintRS()
    
        Const fName As String = "C:Users40815409Desktop2018 Conduent SOCR WEEKLY Status Document "2172018.xlsx"
        Dim cnn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim rs As ADODB.Recordset
        Dim DraftReviewUpdate As Long
        'Dim DraftReview2 As String
        Dim InterimStart As Long
        Dim InterimClose As Long
        Dim FinalStart As Long
        Dim FinalClose As Long
        Dim SQL As String
        Dim i As Integer
           
        'Open connection to our spreadsheet
        Set cnn = New ADODB.Connection
        With cnn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Data Source=" & fName & ";" & _
             "Extended Properties=""Excel 12.0 Xml;HDR=0;IMEX=1"";"
            .Open
        End With
       
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = cnn
        cmd.CommandType = adCmdText
        Set rs = New ADODB.Recordset
         
        ' Query the workheet
        SQL = "SELECT AuditReportNumber, DraftReview FROM [2018Overview$] "
         
        cmd.CommandText = SQL
        'rs.CursorLocation = adUseServer
        rs.CursorLocation = adUseClient
        rs.CursorType = adOpenStatic
        rs.LockType = adLockOptimistic
        rs.Open cmd
        
        For i = 1 To rs.RecordCount - 1
            Debug.Print rs.Fields("DraftReview")
            rs.MoveNext
        Next i
        
        rs.Close
        
    End Sub

    I get the following in the Immediate window:

    6/18/2018
    5/9/2018
    4/9/2018
    5/21/2018
    6/26/2018
    9/27/2018
    9/27/2018
    9/6/2018
    9/18/2018
    Null
    Null
    8/24/2018
    8/24/2018
    8/24/2018
    8/24/2018

    Those Null values above are TBD values in the original Excel file. Then when the code runs to update an existing access table with these values, the Null turns to a blank entry in the table. So I have lost the TBD value during the import process, which I don’t want to do.

    My understanding is that when the query above grabs the field values from Excel it automagically determines the field type is adDate because the first 8 rows are a date. If there is way to change this, or if it has been suggested and I missed it, that would be great.

    The only way that has worked so far is to sort the Excel column so that the TBD are at the top, then when the above code runs it will see 8 text fields instead of 8 date fields and set the field type to text.

    ---

30. Jan 20th, 2019, 01:31 PM

    
    

    #30

    Re: Error on ADO Recordset Update
    

    When I open your DB I only see the table. Is there any code that shows how you did this?

    When you download the zip file…unzip it to a directory of choice. Open the .vbp project file…there’s your code

    ---

31. Jan 20th, 2019, 01:32 PM

    
    

    #31

    

    Thread Starter

    
    Junior Member
    

    

    ---

    Re: Error on ADO Recordset Update
    

    @SamOscarBrown…trust me, I would rather not have to sort the Excel file at all if I can figure out a way to stop the code from defaulting to an adDate type field and making all non-date entries Null.

    ---

32. Jan 20th, 2019, 01:36 PM

    
    

    #32

    Re: Error on ADO Recordset Update
    

    There is a setting in the registry to force Excel to examine every row before guessing. I know that applies to importing into Excel and it may very well apply when exporting from Excel. That was mentioned in post #21 above. However, an app should not typically update user registry settings without permission.

    Chris offered another possible solution by moving data directly from Excel to Access. Maybe that will bypass the auto-guessing since MS office generally cross-talks well with other MS office objects? See post #22 above

    Here’s something I haven’t tested and don’t know if it will work correctly or cause other issues… Have the Excel sheet exported directly to a CSV file with similar logic as Chris’ post #22. The CSV file may need a schema.ini file if Excel will honor it. The schema.ini file dictates the field data types of the CSV. Then the CSV, if written as expected, can be imported into Access via ADO or other methods.

    ---

33. Jan 20th, 2019, 01:52 PM

    
    

    #33

    Re: Error on ADO Recordset Update
    

    And another option, just thinking out loud, is to dynamically create a linked «table» in your Access database to the Excel sheet. This assumes that when done, that linked table can be read as expected, i.e., has TBD field values. If so, you can execute a query directly from the linked table and your Access table, then remove the linked table.

    Before pursuing this, try it first. Open Access and then add an external data source (your Excel sheet) as a linked table (vs. importing it). If Access prompts you with an import wizard, then that may defeat the purpose of dynamically doing this. On the other hand, that could be another solution if the Excel sheet structure never changes. And in that case, you may be able to build a simple Access macro to link the Excel file and execute the macro? Again, just thinking out loud

    ---

34. Jan 20th, 2019, 02:10 PM

    
    

    #34

    Re: Error on ADO Recordset Update
    

    Originally Posted by troybos
    

    When I open your DB I only see the table. Is there any code that shows how you did this?

    Maybe I haven’t explained the problem well enough. Let me try again with some of my real data.

    When I run this code:

    [CODE]

    6/18/2018
    5/9/2018
    4/9/2018
    5/21/2018
    6/26/2018
    9/27/2018
    9/27/2018
    9/6/2018
    9/18/2018
    Null
    Null
    8/24/2018
    8/24/2018
    8/24/2018
    8/24/2018

    Those Null values above are TBD values in the original Excel file

    what values are the TBD values ?

    you can’t have a Database Field with Date and Text at the same Time

    what you could do is set for a TBD Value a Date value say 01.01.1800

    or do it all as Text

    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 — 3900 were counted. with Baby Callas it’s 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

    ---

35. Jan 20th, 2019, 02:17 PM

    
    

    #35

    Re: Error on ADO Recordset Update
    

    @Chris. The DB field is text. Excel is converting «TBD» excel column values to Null when he gets the recordset from Excel because Excel is treating the column as Dates and «TBD» is not a date. OP didn’t mention whether there are also blank column values which would also be converted to Null. But I assume they do exist also, otherwise, the solution is simply to convert all Null from Excel to «TBD» before importing into Access.

    ---

36. Jan 20th, 2019, 02:18 PM

    
    

    #36

    Re: Error on ADO Recordset Update
    

    @Chris…yeah, I proposed a ‘place holder’ date, but OP rejected that. (???) I then suggested treating everything as Strings and later, when needed from the Table, convert everything possible back to dates (of course TBD in that table would not convert, clueing OP that it still needs a date sometime), but OP rejected that. (???) Op is insistent on sorting ‘mixed’ values in the Excel spreadsheet…have no idea why. Gonna just sit back and see how it goes from here on in.

    ---

37. Jan 20th, 2019, 02:19 PM

    
    

    #37

    

    Thread Starter

    
    Junior Member
    

    

    ---

    Re: Error on ADO Recordset Update
    

    @ChrisE…I would like to do it all as Text. Is there a way to stop Access from automatically determining the field type using the first 8 rows of the Excel sheet and force it to read it all in as Text?

    ---

38. Jan 20th, 2019, 02:20 PM

    
    

    #38

    Re: Error on ADO Recordset Update
    

    Originally Posted by troybos
    

    @ChrisE…I would like to do it all as Text. Is there a way to stop Access from automatically determining the field type using the first 8 rows of the Excel sheet and force it to read it all in as Text?

    SEE MY LAST PROJECT! Does EXACTLY that.

    ---

39. Jan 20th, 2019, 02:28 PM

    
    

    #39

    

    Thread Starter

    
    Junior Member
    

    

    ---

    Re: Error on ADO Recordset Update
    

    Working on it…I was doing all of this in Access and am now installing VS so I can open your project. I will let you know how it goes.

    ---

40. Jan 20th, 2019, 02:29 PM

    
    

    #40

    Re: Error on ADO Recordset Update
    

    Originally Posted by SamOscarBrown
    

    SEE MY LAST PROJECT! Does EXACTLY that.

    Hi Sam

    just tried it, works !!

    just the Field size is 255, but that is the smallest problem

    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 — 3900 were counted. with Baby Callas it’s 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

    ---

• 
• VBForums
• Visual Basic
• Visual Basic 6 and Earlier
• [RESOLVED] Error on ADO Recordset Update

Posting Permissions