[solved] INSERT data into mdb file limited to 255 characters

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

[solved] INSERT data into mdb file limited to 255 characters

Post by tmplinshi » 11 May 2015, 01:00

Code: Select all

ComObjError(0)

; Create new mdb file
FileName := "test.mdb"
FileDelete, % FileName
db := ComObjCreate("ADOX.Catalog")
db.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" FileName)

; Open mdb file, and CREATE TABLE...
conn := ComObjCreate("ADODB.Connection")
conn.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" FileName)
conn.Execute("CREATE TABLE Table1(Col1 Text)")

; Insert a 300 length data
Loop, 300
	testData .= "a"
conn.Execute("INSERT INTO Table1 VALUES('" . testData . "')")

; Check the data's length that just inserted
rs := conn.Execute("SELECT * FROM Table1")
MsgBox, % StrLen(rs.Fields("Col1").Value) ; <-- Only 255 characters, not 300

rs.Close(), rs := ""
conn.Close(), conn := ""
Return
Last edited by tmplinshi on 11 May 2015, 01:17, edited 2 times in total.

tmplinshi
Posts: 1604
Joined: 01 Oct 2013, 14:57

Re: INSERT data into mdb file limited to 255 characters

Post by tmplinshi » 11 May 2015, 01:12

Change conn.Execute("CREATE TABLE Table1(Col1 Text)")
to conn.Execute("CREATE TABLE Table1(Col1 Memo)") solved the problem :D

Text --> up to 255
Memo --> up to 65,000

kc6267
Posts: 1
Joined: 06 Oct 2022, 10:00

Re: [solved] INSERT data into mdb file limited to 255 characters

Post by kc6267 » 02 Feb 2023, 09:52

Thanks. Proved very useful.

You can also use text but have to specify character width. I used 255 as an example.
Change conn.Execute("CREATE TABLE Table1(Col1 Text)")
To conn.Execute("CREATE TABLE Table1(Col1 Text(255)")

If I am not mistaken the field will occupy 255 characters worth of hard drive space regardless of its content, including null content. I think the same applies to the Memo data type (which would occupy 65,000), if compression is not used (see docs on compression). Memo data types can result in a much larger than necessary database files.

Post Reply

Return to “Ask for Help (v1)”