Using an Access Database
BlitzMax Forums/BlitzMax Beginners Area/Using an Access Database
| ||
| Am i being really dumb when searching for "database" in the help files and not finding anything? I want to use Maxgui to put a frontend on an Access Database. Can someone point me to the right section of the Help Files so i can read up on it? Cheers Glenn |
| ||
| There is no built-in database access, although there are a couple of 3rd party user modules that allow you to talk to a MySQL or PostgreSQL database... There may be something for MS Access as well, but I'm not sure about that. |
| ||
| if 3rd party then where should i start looking? code archives, google etc |
| ||
| surely BlitzMax has some sort of ODBC connection? |
| ||
| have a look at what gman's done. shame he didnt get a bit more support with his database api and driver idea, very slick... personally I'd steer away from stand alone access (.mdb) files I've had them mince data quite effectivly. You might want to search sqlite on the forums here depending on what you want to achieve |
| ||
| i have downloaded the SQLite wrapper and followed Assaris tutorials. I have also bookmarked the SLQite homepage. Thanks Chris and Xlsior for replying. Cheers Glenn |
| ||
| Gman's MySql module works wonderfully too. Just thought I'd confirm that as I've used it some. |
| ||
| What we need is a cross-platform ODBC database module :-) Skid !?! |
| ||
| I want to use Maxgui to put a frontend on an Access Database. Why oh why would you want to use an Access database in the first place? If you're going to use underpowered, obscure, databases, at least use one that's useful. Like Paradox. What we need is a cross-platform ODBC database module :-) You mean like iODBC? Already suggested it. If memory serves, the answer was no. |
| ||
| Presumably a very simple wrapper for an open, cross-platform, C/C++ based ODBC module (which I'll hazard a guess iODBC is) wouldn't be *that* hard...and if it could be hooked up with skid's revamping of linking maxgui gadgets to "data-sources"... |
| ||
| I'm starting to like the sound of this.. I hope someone starts running with it.. :) |
| ||
| I have some plans for a new cross-platform database module. Another one, you say? I want prepared statements in my SQLs, and I didn't fancy back-fitting that functionality into another module. You know, kind of like :
' get a new query object
Local query:TDatabaseQuery = TDatabaseQuery.Create(db)
' prepare the insert statement
' by preparing it once, the database can reuse it on succesive inserts which is more efficient.
query.prepare("INSERT INTO person values (NULL, ?, ?)")
If db.hasError() Then
errorAndClose(db)
End If
' iterate round the array inserting new entries
For Local i:Int = 0 Until names.length
query.bindValue(0, TDBString.Set(names[i][0]))
query.bindValue(1, TDBString.Set(names[i][1]))
query.execute()
If db.hasError() Then
errorAndClose(db)
End If
Next
I think I also want "Iterator" support for result sets.. which would tie in nicely with Lists and suchlike. The nice thing with ODBC is, that once you have it working with one driver, it should (in theory) automagically work with all drivers. And why have database-specific modules as well as ODBC? ODBC is a bridge, so it's generally not as efficient as hitting the native drivers. MySQL is currently being a bit of a pain in the arse with it's bindings, but it's getting there. ...the answer was no. Who said no? BRL? That's not very surprising really. Once MySQL is working, I'll have a go with ODBC. Can't be *that* hard, as Mark says ;-) |
| ||
| I amazed by the sheer amount of thing you're working on Brucey.. :) Maybe BRL should hire you!!?! ;) |
| ||
| Maybe BRL should hire you!!?! ;) Hrmmm... |
| ||
| Maybe BRL should hire you!!?! ;) I doubt NZD translated into GBP translated into ZAR would go very far in these parts :-p Hrmmm... I vote this one as Mr Ducks most interesting post of the year to date :-) |
| ||
| Amazing how many different data types there are... ..except on SQLite (where there are about 4, all told). Am mostly happy with the SQLite and MySQL implementations now (except for some data type issues (blobs and date/time/datetime) Other than that, my tests are working for standard execution and prepare/execute on those two. Next up, ODBC, I suppose. Since iODBC doesn't compile on Win2k, I've decided to plug into mingw's libodbc32 stuff on Windows, and will (attempt to) use iODBC on the rest. If the statements are true (from the iODBC website/docs) I shouldn't need to change anything between the two implementations - we'll see... |
| ||
| If the statements are true (from the iODBC website/docs) I shouldn't need to change anything between the two implementations chokes on coffee! of course it *should* work an all three in -theory- oh look its a flying pig! I look forward to seeing your db modules :) |
| ||
| Bit of a key-bashing session over the weekend. ODBC on Linux is working (at least, all my standard tests are!). Tested connecting to MySQL via its ODBC Connector plugin. Need to give it a go on Windows to see how compatible it is, still. And I've yet to test any of it on Mac - cuz I've been busy getting it working firstly :-p SQLite seems to be good too. And MySQL is done but for a seg-fault on Linux when doing some binding. Me thinks I went wrong somewhere there... I might try connecting to Oracle via ODBC if I get a chance. Would be a nice example ;-) |
| ||
| Interestingly, I've just ran all my ODBC tests on Windows... and would you believe it... it works!! I say... great job to the iODBC developers!!!! (for the *nix port) :-) |
| ||
| And for interests sake... I've also got it Dropping/Creating a table, Inserting and Selecting from an Access Database, through ODBC. Some notes. * Access has its own peculiar syntax. * Access doesn't handle prepared statements * It's pants ;-) |
| ||
And of course, Oracle...Building untitled2 Executing:untitled2.debug.exe DebugLog:Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production from this... Local query:TDatabaseQuery = db.execute("select * from v$version where banner like 'Oracle%'") |
| ||
| and the only data store that has garbled my data award goes to..... access.mdb (wild cheering from the crowd) great to hear odbc is going so well, I know of a few web designers that use a mysql db with odbc so they can use access's gui query generator I was wondering.... how difficult to make would a sql gui be do you think, you know graphical relations, drag and drop some fields and out pops a generated sql. |
| ||
| ...how difficult to make would a sql gui be do you think... Me thinks that is out of the scope of my db framework ;-) However, if you know what to select from the database, all the information is in there to build such a tool. I'm not exposing all of the ODBC api (of which there seems to be a lot) since it's a generic wrapper, so there may be more stuff you can use to get at more information. |
| ||
| I was wondering.... how difficult to make would a sql gui be do you think, you know graphical relations, drag and drop some fields and out pops a generated sql. Fairly complex. Or you could just use something like MicroOLAP Database Designer (got versions for both MySQL and Postgres). |
| ||
| Alas the solutions you suggest are costly and only seem to run on some toy OS I don't have I looked at the source to kexi and doing graphical relationships and simple queries ala access actually doesnt look too much of a nightmare I'll have a look at your wrapper when its out and have a play with a few ideas... |
| ||
| The official word from BRL: Hi Glenn, I'm pretty sure this won't be coming any time soon -- Blitz is heavily biased towards game development and Access support isn't something we've ever found in real demand from Blitz users over the years. Sorry! James/Blitz Support. Glenn wrote: > I don't suppose it is on the BRL "to do" list? > I have an access database with a VB6 frontend which i want to convert > to BlitzGui and having to recreate the database is just an extra > effort i hadn't expected. > > Cheers > Glenn > >> Hi Glenn, >> >> Just to confirm, there's no built-in database access in Blitz, but I >> see from the thread that you've found a couple of the 3rd-party >> options anyway. >> >> James/Blitz Support. >> >> >> glenn@... wrote: >>> Hello, >>> I have just started a thread in the beginners area for how to >>> connect to an MS Access Database. Initial feedback is not promising. >>> >>> Can i connect to an Access Database with Maxgui? >>> >>> Cheers >>> glenn Also for Flameduck: I guess like a lot of people i have grown up using MS Access. Doesn't matter whether it is good or not, it is just what i know and and familiar with. Proper developers at work scoff at Access too, but when i need something done in a hurry the IT Dept doesn't have the time or resource to do it properly in SQL Server. I can query the SQL databases but i can't create anything. So i pull the data to MS Access where i can create new tables, do my queries and use the macros for the email functions. It isn't the best way to do it, nor is it "technically supported" by IT, but it works and gets the job done. I work in Operations with a $200 Million wage budget each year, and when i need to analyse a change to Operational Procedures, i really can't wait 6 months for IT to be free to help me. And i used VB6 for a front end because thats what i grew up with too. I LOVE using BLitzMax, although for the last few months i seem to spend more time reading these forums than actual programming. The projects i use to teach myself, other than the great tutorials to be found here, are replicating small tasks i have done in VB and MS Access, plus i have a simple little game i wrote in BlitzBasic when it was on the Amiga. Well it is 8.30am here in New Zealand so i suppose i better wander off to work for another "fun filled" day, till i can check the forum again when i get home. I hope you all have a great day and i am keen to follow Brucey's DB Framework and Chris C's modifications. Cheers Glenn |
| ||
| Don't feel estranged there Glenn. I too started with MS Access and dBase databases! I've since moved along to SQL Server, Oracle, DB2, and Informix. Yup... been all over as far as those go. I also admit that a "down and dirty" deal can be easily done using MS Access, but I also scoff at its use for much else than personal or really quick-fix solutions (extremely temporary). Don't fret over using VB6 or any of the VB languages with a database. Its extremely common for any business to use VB with a database. Mostly, database work doesn't demand cutting-edge speeds that C/C++ or other lower level languages offer. Working a GUI with a database should be pretty casual unless there is an explicit real-time need for the speed. Even then, you will wait on the database. I understand the wait period for IT. I'm a professional software engineer and we have a HUGE plate filled with projects eagerly awaiting our time! |
| ||
| Chris C's modifications. god no!, now what have i got myself roped into, I was just gonna cobble some stuff up on top of Brucies mod once he gets done... BTW I often used to use access as a front end to a number of database backends via ODBC, using linked tables it made a fairly reasonable gui. I looked at it again just recently, a mates on some beta program and gets sent loads of junk from bill and access sadly seems to be a *lot* more bloated and slow nowadays (does anyone use all that stuff! talk about feature creep!). It would be nice to see some cross platform system to decouple RAD GUI and backend that was both flexible and light weight, after 20+ years I'm still looking... now I think about it I remember talking to this sales guy more than ten years ago now, he said in a few years everyone would be programming from graphical blocks and drawing connecting lines, it would be so efficient and easy for the computer to optimise that even OS's would be written using it, wonder what happened to that guy... |
| ||
| I'm just tidying up the docs - my *favourite* part (Not!). Hate to release a module without reasonably full documentation - (hey, what can I say... I personally don't like downloading someone else's module and having to read the module source-code to work out how to use it!! And for those who say, "what do you want, it's free!?", I say, bollocks ;-) Anyhoo... The ODBC module will have in the tests folder an Access test example (cuz I needed to see for myself if it would work!). Spent 4-5 hours on Friday (ack!) fixing a single MySQL bug on Linux/Mac... but am happy to say it works now :-) Am currently also almost finished the PostgreSQL module, but I'll probably release the others before that's done. (although it depends how long it takes to get the docs finished really). I use Oracle mostly at work. It's not as nice as Informix though (which I've learned over the weekend, spawned from the same home as Postgres!). Also use MySQL as the backend for tools/utils I make for the office. ;-) |
| ||
| i look forward to this one! another quality Brucey product and the price is right (good game, good game) :o) |
| ||
btw, I also added the ability to retrieve records either in the traditional style:While query.nextRow() Local record:TQueryRecord = query.rowRecord() .... Wend or the Max way: For Local record:TQueryRecord = EachIn query .... Next which some people like ;-) Will try to get most of the docs done this evening... *sigh* |
| ||
| Hello. Looking forward to seeing this. MS Access as a tool is fine in what it does, provided as others have mentioned, that you're not too ambitious with it. Using it as a front end to SQL Server it's a great tool. Goodbye. |
| ||
| Okay... I've uploaded the stuff to the website : http://brucey.net/programming/blitz/index.php Currently there are drivers for MySQL, ODBC, and SQLite. PostgreSQL driver is still being worked on. Included in the ODBC module is an Access test app, just to prove to myself it worked. Have tested ODBC on Windows to Access and Oracle, and on Linux to MySQL (using MyODBC). MySQL was tested on Windows, Mac and Linux. SQLite was tested on Windows, Mac and Linux. Let me know if you have any problems (with the modules!). Can't help you with SQL I'm afraid. There are plenty of docs online for that ;-) And... I can't help you set up ODBC stuff either. (do you really want everything handed to you on a silver platter? :-p ) Obviously, there may still be some bugs lying around. Please report if you find anything. General comments are also appreciated. PS. If you just want to mess about with it, I would say the SQLite database is the easiest, as it requires no configuration. |
| ||
| Excellent work seems to work well overall. Some tests need to have have "db.exceute" changed to db.executeQuery to run (SQLite samples, 1- 4) I can't seem to compile the MySql test 1 , I get: C:/xxx/BlitzMax/bin/ld.exe: cannot open C:/xxx/BlitzMax/tmp/ld.tmp -LC:/xxx/BlitzMax/mod/bah.mod/dbmysql.mod/lib/win32: Invalid argument Build Error: Failed to link C:/xxx/BlitzMax/mod/bah.mod/dbmysql.mod/tests/test_01.debug.exe |
| ||
| Oh... yeah... ho hum My fault ;-) (my fault for hacking stuff so much) That uses a currently unreleased fix to bmk which allows linking outside of BlitzMax/lib. (there's a page in Module Tweaks that shows the actual change) For now, I guess you can copy the "libmysql.a" file (that is in lib/win32) to your BlitzMax/lib folder and comment out that Import "-L..." line. Sorry :-/ ... coding on Linux/Mac is *so* much easier ;-) |
| ||
| Some tests need to have have "db.exceute" changde to db.executeQuery to run (SQLite samles, 1- 4) I thought I'd gone over all of them and fixed that. Someone needs to write a Refactoring tool for the IDE :-p |
| ||
| The new Windows DBMySQL module (on the site) now expects libmysql.a to be in the BlitzMax/lib folder, which will need to be copied there from the module lib/win32 folder. Finally got the PostgreSQL driver running on both Windows and Linux. Again, a .a file will need placing in the lib folder for that one. Works very well, now that I've worked out how to enable TCP/IP on the server ;-) Need to try out some Mac tests this evening... Am also interested in adding a Firebird driver, but there's an issue with BMK (yes, again...) which stops me dead in my tracks at the moment. Well, I've amended my bmk, and the library compiles now, but it's a bit of a pain... Apart from those, I can't think of any other databases that would be good to have drivers for. (Yeah, there's Oracle, but for now I'm happy with the ODBC to talk to that. For now... ;-) |
| ||
| If someone can give a report on any success connecting to Access databases, it would be much appreciated. Will I really need to add documentation to the ODBC module to explain how to configure DSNs? |
| ||
| Sorry guys, I am a newbie to using someone elses modules. I have copied the bah.mod folder to the blitzmax mod folder. When i tried to use the ODBC (access) test1 file it gives me an error of : Building test_01 Compiling:test_01.bmx Compile Error: Can't find interface for module 'bah.database' [C:/Program Files/BlitzMax/mod/bah.mod/dbodbc.mod/dbodbc.release.win32.x86.i;10;1] Build Error: failed to compile C:/Program Files/BlitzMax/mod/bah.mod/dbodbc.mod/tests/test_01.bmx Process complete Unfortunately i have no idea what this means... Any help appreciated Glenn |
| ||
| figured it out... I often hear you all talking about rebuilding modules, so i clicked on it. That told me that bah.database wasn't available. Whan i checked i found i hadn't copied that folder across. All i had moved was the bah.dbodbc folder. so i moved the other 3 folders and recompiled the modules. The test works now. On a side note, i syncmod every few days. do i need to rebuild modules after this? i saw heaps (1/3 of files) needed to be compiled. is this expected? i don't really know anything about this. Cheers Glenn |
| ||
| @Glenn, interesting thread, I'm just about to submit an (access) relational database college assignment in two days. My weeks pretty full but I'll look forward to giving this code a whirl this weekend. I to am getting to be a forum addict - it's all too easy to be sidetracked from finishing the hum drum stuff. Regards - Mark. |
| ||
| Brucey i still need a little help. I have copied your modules to the mod folder. I have moved the DSN (maxtest) to my default dsn folder (C:\Program Files\Common Files\ODBC\Data Sources) and done a reboot. When i run Test1 i get the following error: Building test_01 Compiling:test_01.bmx flat assembler version 1.66 3 passes, 7134 bytes. Linking:test_01.debug.exe Executing:test_01.debug.exe DebugLog:(2) Error opening connection : [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified : Process complete I am familiar with DSNs from using Visual Basic. I have done things like creating my own DSN (file DSN), creating my own database and putting in extra debuglog lines just to confirm where the error is, but i still get this error. I know it will be something really simple but i can't see it. Can you help? Cheers Glenn |
| ||
| I got the access test running by editing the enclosed DSN file in a text editor. I just changed the path to fit where the files are located on my computer. |
| ||
| Here is my LoadDatabase line: Local db:TDBConnection = LoadDatabase("ODBC", "Glenn", Null, Null, Null, Null, "GlennDSN") where Glenn is a database (access 2003) saved as c:\Glenn.mdb GlennDSN is the DSN entry, as below: [ODBC] DRIVER=Microsoft Access Driver (*.mdb) UID=admin UserCommitSync=Yes Threads=3 SafeTransactions=0 PageTimeout=5 MaxScanRows=8 MaxBufferSize=2048 FIL=MS Access DriverId=25 DefaultDir=C:\ DBQ=C:\Glenn.mdb This is the default data and i haven't modified it in any way. I am using Test_01 from the odbc folder Here are the first few lines SuperStrict Framework BaH.DBODBC ' database dsn name Local db:TDBConnection = LoadDatabase("ODBC", "Glenn", Null, Null, Null, Null, "GlennDSN") If Not db Then DebugLog("Didn't work...") End End If If db.hasError() Then errorAndClose(db) End If DebugLog( "Database Loaded") I keep getting db.hasError(). Any ideas appreciated. Regards Glenn |
| ||
| Hi Glenn. As you know there are several ways to create a DSN. The Access example I provided uses the FileDSN method, which requires a full path to the .dsn file. The other method requires the ODBC data source to be configured through the system. To do this on my win2k box, I go to Control Panel, Administrative Tools, and open Data Sources (ODBC). On the UserDSN tab, I click Add, choose Microsoft Access Driver, and give it a Data Source Name of GlenDSN. I then Select the .mdb file, and finish up by hitting OK. This adds an internal DSN to my installation. I had a look in C:\Program Files\Common Files\ODBC\Data Sources, but that folder is empty on my box, so I guess it puts it somewhere else. (perhaps that is a SystemDSN?) Anyhoo... changing the Access test prog from: Local db:TDBConnection = LoadDatabase("ODBC", "maxtest", Null, Null, "brucey", "brucey", CurrentDir() + "/maxtest.dsn")to [/code]Local db:TDBConnection = LoadDatabase("ODBC", "maxtest", Null, Null, "brucey", "brucey", "GlenDSN")[/code] it still works. Remember, if you specifically want to access a .dsn file, you will need to provide a full path to it. Otherwise, I believe configuring it through the proper application is the best way. Of course, I could be talking complete nonsense ;-) (but my tests on all three platforms have worked best when I've configured things through the appropriate GUIs) :o) |
| ||
| Well that was simple. I changed from FileDSN to UserDSN and i don't get that same error. I was originally going to use this but once i had copied your maxtest dsn file to the specified directory i saw it show up on the File DSN menu, not User DSN. Now i am getting this. I am currently trying to diagnose it... DebugLog:(3) Error executing statement : [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement. : And the line it doesn't like is DebugLog:CREATE TABLE person (id integer primary key AUTO_INCREMENT, forename varchar(30), surname varchar(30) ) odd that it works for you but not me. again it must be something really simple i am not doing properly... Oh well, the joy of programming... Cheers Glenn |
| ||
| Well i have confirmed the database is opening properly and existing tables can be seen (used test_05 for this and a table i had created). I know it is dropping the table as a second call to test_05 shows the table is gone. It is now reporting a error with an error number. I am checking for what this means... Building test_01 Compiling:test_01.bmx flat assembler version 1.66 3 passes, 9658 bytes. Linking:test_01.debug.exe Executing:test_01.debug.exe DebugLog:Database Loaded DebugLog:Database is Opened DebugLog:what tables exist DebugLog: 1. person DebugLog: 2. tblTest DebugLog:Table Dropped DebugLog:what tables exist after dropping Person DebugLog: 1. tblTest DebugLog:s = CREATE TABLE person (id integer primary key AUTO_INCREMENT, forename varchar(30), surname varchar(30) ) DebugLog:Tried to Execute Query DebugLog:(3) Error executing statement : [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement. (-3551) : Process complete |
| ||
| The problem seems to be AUTO_INCREMENT. Take this word out and the table is created. of course the next section of code tries to pass NULL to an auto-incrementing value and fails, because i took auto-increment out... So i have 2 sets of Debug: 1::: Auto_increment IN Building test_01 Compiling:test_01.bmx flat assembler version 1.66 3 passes, 7804 bytes. Linking:test_01.debug.exe Executing:test_01.debug.exe DebugLog:Database Loaded DebugLog:Database is Opened DebugLog:Table Dropped DebugLog:s = CREATE TABLE person (id integer primary key AUTO_INCREMENT, forename varchar(30), surname varchar(30) ) DebugLog:Tried to Execute Query DebugLog:(3) Error executing statement : [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement. (-3551) : Process complete 2::: Auto_increment OUT Building test_01 Compiling:test_01.bmx flat assembler version 1.66 3 passes, 7776 bytes. Linking:test_01.debug.exe Executing:test_01.debug.exe DebugLog:Database Loaded DebugLog:Database is Opened DebugLog:Table Dropped DebugLog:s = CREATE TABLE person (id integer primary key, forename varchar(30), surname varchar(30) ) DebugLog:Tried to Execute Query DebugLog:No Error creating the table DebugLog:(3) Error executing statement : [Microsoft][ODBC Microsoft Access Driver] Index or primary key cannot contain a Null value. (-1053) : Process complete Not quite sure where i go to now. i guess i start looking at the modules for a keyword listing? According the MS Access 2003 help the value should be PRIMARY KEY | UNIQUE | NOT NULL Hmmm. Just entered the above and got passed the auto_increment problem BUT now have Building test_01 Compiling:test_01.bmx flat assembler version 1.66 3 passes, 7808 bytes. Linking:test_01.debug.exe Executing:test_01.debug.exe DebugLog:Database Loaded DebugLog:Database is Opened DebugLog:Table Dropped DebugLog:s = CREATE TABLE person (id integer PRIMARY KEY UNIQUE NOT NULL , forename varchar(30), surname varchar(30) ) DebugLog:Tried to Execute Query DebugLog:No Error creating the table DebugLog:(3) Error executing statement : [Microsoft][ODBC Microsoft Access Driver] The field 'person.id' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field. : Process complete so now i guess the line: db.executeQuery("INSERT INTO person values (NULL, '" + names[i][0] + "', '" + names[i][1] + "')") is a problem. No rest for the wicked... |
| ||
| Ahh... wondered if you'd pick up on those issues. The SQL you use for ODBC is *generally* cross-database safe - that is, if you use ANSI-standard SQL. However, many databases, like Access, use non-standard syntax. Which is the problem you have come across ;-) The actual tests for the ODBC module are assuming an ODBC connection to a MySQL database. So, they'd need to be "tweaked" to work on Access. To create the auto incrementing column in my example, I used: id counter constraint pk primary key I guess, "counter" is the important bit. Remember, this won't work for say, MySQL. (In Access,) To insert into a table where one field is auto-incrementing, you need to leave out the field as part of the insert. In MySQL you can pass Null. In Postgres, I think you've to pass 0. (Well, it's 0 in Oracle...) Thus, to insert into person, the easiest way is to show what fields you intend inserting into :
INSERT INTO person ( forename, surname )
VALUES ('brucey', 'bonus');
Which would cause the missing "id" field to be populated with the "next" value in its sequence. I suggest you try reading the Access syntax documentation. Alas, as I've discovered, it's close to the worst documentation I've read when trying to find something specific. (it took me about 20 mins to find out I had to use "counter" for an auto-incrementing column). I suggest you look at the access example to get an idea of the difference between the SQL Access needs and what MySQL requires. btw, the reason I didn't do *all* the tests in Access, is because I haven't the time to work it out myself... since my Win32 time is relegated to the work PC. :o) |
| ||
Oh.. and if you don't want it to auto-increment (the id field), you'll need to keep track of it yourself...SELECT max(id) from person and then add one to that value when you insert... Using an automatic counter is less hassle :-) |
| ||
| Many thanks Brucey. I should have picked up the "don't use NULL for autonumber fields" myself (because thats what i do in VB) but i have been off work for the last 2 days due to being sick, pretty much the only reason i have had the time to play... Your modules are fantastic and i am sure heaps of people will find them very useful. I wonder if BRL will incorporate them? I don't seem to have been the only person wanting database support. Cheers Glenn |
| ||
| Just wanted to say thanks for the work and I am one of those heaps of people who find this useful ;) |
| ||
| Pleasure as ever :-) |
| ||
| I thought i would add this test code for any Microsoft Access users wanting to use Bruceys module. and here is the final output: Building Bruceys-MS-Access-Module_Test Compiling:Bruceys-MS-Access-Module_Test.bmx flat assembler version 1.66 3 passes, 11118 bytes. Linking:Bruceys-MS-Access-Module_Test.debug.exe Executing:Bruceys-MS-Access-Module_Test.debug.exe DebugLog:Database Loaded DebugLog:Database is Opened DebugLog:Table Dropped DebugLog:s = CREATE TABLE person (id counter PRIMARY KEY UNIQUE NOT NULL , forename varchar(30), surname varchar(30)) DebugLog:Tried to Execute Query DebugLog:No Error creating the table DebugLog:s = INSERT INTO person ( forename, surname ) VALUES ('brucey','bonus') DebugLog:s = INSERT INTO person ( forename, surname ) VALUES ('brucey','bonus') DebugLog:s = INSERT INTO person ( forename, surname ) VALUES ('brucey','bonus') DebugLog:First Insert SUCCEEDED DebugLog:Now read the records DebugLog:Name = brucey bonus DebugLog:Name = brucey bonus DebugLog:Name = brucey bonus DebugLog:s = INSERT INTO person ( forename, surname ) VALUES ('Alfred', 'Aho') DebugLog:s = INSERT INTO person ( forename, surname ) VALUES ('Brian', 'Kernighan') DebugLog:s = INSERT INTO person ( forename, surname ) VALUES ('Peter', 'Weinberger') DebugLog:Second Insert SUCCEEDED DebugLog:Now read the records DebugLog:Name = brucey bonus DebugLog:Name = brucey bonus DebugLog:Name = brucey bonus DebugLog:Name = Alfred Aho DebugLog:Name = Brian Kernighan DebugLog:Name = Peter Weinberger DebugLog:and finally CLOSE the database Process complete Cheers Glenn |
| ||
| Glad you've sorted it all out at last :-) |
   |