Free dll to work with Access database

[bule]

What would be nice is if someone could create a function to parse the results of a select query into a Lua table in the same format as SQLite.QueryToTable() does.

Perhaps these two links may help in thinking:

http://www.indigorose.com/forums/showpost.php?p=100721&postcount=10

http://www.indigorose.com/forums/showpost.php?p=100815&postcount=21

[lnd]

i need Example to get and put text from INPUT object

i need Example to get and put text from INPUT object

[Derek]

i need Example to get and put text from INPUT object

So you said, already! Take a look in the help file at Input.GetText and Input.SetText

http://www.indigorose.com/webhelp/ams60/Program_Reference/Objects/Input/Actions.htm

-

[Dermot]

Thanks Bule, that got me on the right track. This seems to work good.

function xMDBQueryToTable(db, Query)

	if db ~= "" then
	
		if File.DoesExist(db) then
			
			if Query ~= "" then
			
				--define variables:
				local Rows=0;
				local Columns=0;
				local ColumnNames={};
				local Data={};
				
				result = DLL.CallFunction(_SourceFolder.."\\AutoPlay\\Docs\\xMDB.dll", "Query_Select_MDB", "\""..db.."\",\""..Query.."\"", DLL_RETURN_TYPE_STRING, DLL_CALL_STDCALL)
				
				if String.Left(result, 5) ~= "Error" then

					if result ~= "NO_DATA" then

						-- Get all the rows returned
						tbl = DelimitedStringToTable(result, "|")
						
						if tbl then
	
							-- Get column names from first row
							tblCols = DelimitedStringToTable(tbl[1], ";")
							
							if tblCols then
						
								-- Loop through the columns
								for nCol=1, Table.Count(tblCols) do
								
									Columns = Columns + 1
									ColumnNames[Columns]=tblCols[nCol];

								end
								
							end
							
							--Get the data
														
							for nRow = 2, Table.Count(tbl) do
							
								Rows = Rows + 1
								Data[Rows]={};
								tblRow = DelimitedStringToTable(tbl[nRow], ";")
								
								if tblRow then
								
									for nCol = 1, Table.Count(tblRow) do
									
										Data[Rows][ColumnNames[nCol]]=tblRow[nCol]
										
									end
									
								end
								
							end

							tbl = nil
							tblCols = nil
							tblRow = nil
							
							return {Rows=Rows,Columns=Columns,ColumnNames=ColumnNames,Data=Data};
			
						end
					else
					
						Progress.SetVisible("Prog", false)
						Dialog.Message("No Data", "No records returned.", MB_OK, MB_ICONINFORMATION)
						
					end
				else
				
					Dialog.Message("Failed", result, MB_OK, MB_ICONEXCLAMATION)
					return "Error"
					
				end
			
			else

				Dialog.Message("No Query", "Please enter an select query", MB_OK, MB_ICONEXCLAMATION)
				return "Error"
				
			end
		
		end
		
	else
	
		Dialog.Message("No Database", "Please select a database first", MB_OK, MB_ICONEXCLAMATION)
		return "Error"
		
	end	

end

[bule]

Nice, but what will happen if there is a ; or | in a string stored in the table in the database?

[Jonas DK]

Nice.. :yes

If I may sugest using Lua, you couldt use the ODBC driver from LuaSQL

http://www.keplerproject.org/luasql/index.html#overview

That way you could actually just take the function Bule made for the MySQL version of this and use it directly as I understand it.

Havent tried it though but the LuaSQL also has drivers for Oracle and JDBC
so the incorporation would be huge if you just had to call the right driver and reuse the functions.

You can also find a refinde version of Bules function at:

http://www.indigorose.com/forums/showthread.php?t=19965

As I saied, just a surgestion.. havent tried it.

But again I like what you did Dermot, It looks great (havent testet that either yet..) and just will writting this post I have allready come up with a few apps where it would be handy.

I do have a question. does can it use a database that was created in Access? using linked tables from a SharePoint Server? I that case the Dll you made is a very powefull tool that give access to all the functions of MS Access, you couldt even use the report generater in access to print reports from the database... Did a project doing this a while back, but couldent control the data from AMS so I had to put the data in the database manually and then put the database in my AMS project to use the reports.
by using a commandline arg for access.

I'm rambling on now so I think I'll stop here before I get in to deep...

Cheers,
Jonas

[Dermot]

@ Bule
Yes maybe I didn't use the best delimiters but they can be changed very easily. What I might do is add them as parameters when calling the dll. That way the user can use what ever they want

@ Jonas DK

If I may sugest using Lua, you couldt use the ODBC driver from LuaSQL

You could use LuaSQL but in my tests it always causes an error on Vista. In fact anything that uses compat-5.1 causes an error on Vista. Plus I just found this approach to be much easier.

I do have a question. does can it use a database that was created in Access? using linked tables from a SharePoint Server?

Yes you can use a database that was created in Access. It does work with regular linked tables (linked to another access db) but I don't know about SharePoint.

you couldt even use the report generater in access to print reports from the database

I don't think so, just data. The idea is you can use this dll without having a copy of Access installed. You should be able to use LuaCom to control Access and run reports. Haven't tried it though.

[Dermot]

New version available. Use the links in the first post.

Changed the delimiters a little so that you should not run into problems if the delimeters are in the data.

Column delimeter is ";;"
Row delimeter is "||"

I have also included an updated example which includes a function to take the results from a select query and create a lua table just like the SQLiteQueryToTable function does.

[lnd]

how can i save and delete data from the DB???

how can i save and delete data from the DB???
i have a input1 and input2 how can i save the text in the input1 and input2 to the DB and how can i delete a data (or row) from the DB???

[bule]

I would use a more obscure delimiters;
for example:
Column delimeter: ";§;"
Row delimeter: "|§|"

[Dermot]

how can i save and delete data from the DB???
i have a input1 and input2 how can i save the text in the input1 and input2 to the DB and how can i delete a data (or row) from the DB???

The example app shows you how to add data to the database. Everything is done using SQL.

Add data from Inputs to db.

FirstName = Input.GetText("Input1")
LastName = Input.GetText("Input2")

INSERT IGNORE INTO Contacts(FirstName, LastName) VALUES('"..FirstName.."','"..LastName.."'")

Delete data from db

FirstName = Input.GetText("Input1")
LastName = Input.GetText("Input2")

DELETE FROM Contacts WHERE FIrstName = '"..FirstName.."' AND LastName = '"..LastName.."'"

Obviously you will need to chnage the filed anems etc to suit, don't just copy and paste it and expect it to work. Look at the example project for how to call the dll.

[lnd]

i need the name of the "DLL.CallFunction" to call the Function

i need the name of the "CallFunction" to call the Function to save and name of the Function to delete. or if you can give me Example in file to save and delete data from input object. PLZ
i need to save more then 1000 words can i yus thex text table???

[Dermot]

The sample app as what you need.

Add a new record:

DB = "Path to your Access database"

FirstName = Input.GetText("Input1")
LastName = Input.GetText("Input2")

Query = "INSERT IGNORE INTO Contacts(FirstName, LastName) VALUES('"..FirstName.."','"..LastName.."'")"

result = DLL.CallFunction(_SourceFolder.."\\AutoPlay\\Docs\ \xMDB.dll", "Query_Update_MDB", "\""..DB.."\",\""..Query.."\"", DLL_RETURN_TYPE_STRING, DLL_CALL_STDCALL)

if result == "OK" then
Dialog.Message("Success", "Record added.", MB_OK, MB_ICONINFORMATION)
else
Dialog.Message("Failed", result, MB_OK, MB_ICONEXCLAMATION)
end

Delete record:

DB = "Path to your Access database"

FirstName = Input.GetText("Input1")
LastName = Input.GetText("Input2")

Query = "DELETE FROM Contacts WHERE FIrstName = '"..FirstName.."' AND LastName = '"..LastName.."'""

result = DLL.CallFunction(_SourceFolder.."\\AutoPlay\\Docs\ \xMDB.dll", "Query_Update_MDB", "\""..DB.."\",\""..Query.."\"", DLL_RETURN_TYPE_STRING, DLL_CALL_STDCALL)

if result == "OK" then
Dialog.Message("Success", "Record deleted.", MB_OK, MB_ICONINFORMATION)
else
Dialog.Message("Failed", result, MB_OK, MB_ICONEXCLAMATION)
end

i need to save more then 1000 words can i yus thex text table???

Do you mean 1000 fields or 1000 words in one field? 1000 fields would not be practical.

[lnd]

i need to save 1000 words in one field PLZ give me example

i need to save "1000 words in one field" PLZ give me example in AMS file PLZ to save and delete data from the database

[Dermot]

i need to save "1000 words in one field" PLZ give me example in AMS file PLZ to save and delete data from the database

I just did. The Input can have 1 word or 1000 words. You have to make sure that the field in the database is of the right type, Memo I believe.