Android Question Is it possible to connect Android Basic4Android to Access database on local network

[Gearcam]

Is this now possible to connect Basic4Android directly to a Access backend database stored on our local network.

Not over the internet but just using the Wi-Fi available

The last time I did this I had too have an ASPX page running on the remote server but that was over the internet.

Server 2008 R1

Thanks
Steve

 

[Tron]

OK.
Assuming your webserver is running.
Extract the zip into the WEB-Root folder (e.g. C:\inetpub\wwwroot\Access)
should look like :

Dont change any folder names !

run your browser inside your Server like:
http://localhost/Access
(here you can get Infos about exceptions)

run your browser outside your Server like:
http://192.168.216.4/Access
or (if a servername is defined e.g "server.com")
http://server.com/Access

To choose the database and a test select you can change the entries in the connectionstring segment in the web.config file.
There is also the location where your database should be found.

connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Nordwind 2007.accdb;Persist Security Info=False;

In the "real" operation with B4A these parameters will be sent as "header Parameters"

But for now you can try the connectivity first.
(and pray that the connection string works)
in advance :http://social.msdn.microsoft.com/Fo...-registered-on-the-local-machine?forum=vstsdb
greets Jens

 

[Gearcam]

ok I have installed it but when I do :

run your browser inside your Server like:
http://localhost/Access
(here you can get Infos about exceptions)

I get error The webpage cannot be found

I have done nothing with IIS7

I have restarted the server and inside IIS the Access info is visible

But it will not work

 

[Gearcam]

I need to add the port number in like this
http://localhost:90/Access

it does run but says
*** ERROR: The 'Microsoft.JET.OLEDB.4.0' provider is not registered on the local machine.

the web.config is now like this

  <connectionStrings>
    <add name="SQLConnectionString" connectionString="Provider=Microsoft.JET.OLEDB.4.0;Data Source=V:\Job #\Nordwind.mdb;Persist Security Info=False;" />
    <add name="SQLtest" connectionString="Select * from Kunden" />
  </connectionStrings>

I have to use 32 bit drivers as the version of office 2007 is 32bit but it runs on a 64 bit server

I have installed the 2007 Office System Driver

Help !!

Steve

 

[Tron]

Hello Steve.
First thing is, you got access to the handler. Congratulations.
Second thing is, that you also got the problems I had before with attaching access.

Try to find a correct driver
http://social.msdn.microsoft.com/Fo...-registered-on-the-local-machine?forum=vstsdb

http://www.microsoft.com/de-de/download/details.aspx?id=23734

Try different connection strings.
https://www.connectionstrings.com/access/

greets Jens
(or install access 2010 x64 (additionally))
(or http://office.microsoft.com/en-us/a...soft-sql-server-database-adp-HP005274586.aspx)
and
http://answers.microsoft.com/en-us/...database/be8c0ad4-d8fd-48e0-9026-b95d84135820
further links could be helpful
http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx

 

[Gearcam]

Thanks I have tried the 2007 driver and the 2010 driver neither work
is there anything I need to do in IIS as the server is 64 bit and I am trying to use 32 bit ?

Steve

 

[Gearcam]

ok I have set in IIS the .net framework to 4.0
Also set enable 32-bit applications to true this is in Advanced settings

Now I get an error saying path not correct !!
Getting there !!!!

 

[Gearcam]

the path had to be the full path not the shared path so it works !!!

<addname="SQLConnectionString"connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\datatest\northwind.mdb;Persist Security Info=False;" />

So now to the B4A app !!


Tried to connect to my backend I want to use.
Many PC's are connected via Access frontends for normal use, the Backend is not open on the server.

but I get an error
*** ERROR: Could not use xxxxx.accdb'; file already in use.

its the same if I test with the northwind.mdb if I open it on the server and try to connect I get the file in use error

Steve

 

[Tron]

Hi Steve.

So now to the B4A app !!

Ok.
Do you have an App already, or started a new B4A project ?

Do you intend to use :
http://www.b4x.com/android/forum/threads/rdc-simple-way-to-create-your-own-back-end-database.31616/
http://www.b4x.com/android/forum/threads/remote-database-connector-connect-to-any-remote-db.31540/

Did you test reading database tables simultanously with internal android browsers and other browsers ?

Jens

 

[Gearcam]

I have to solve the file in use problem first

Any thoughts ?

Steve

 

[Tron]

.. any errors are catched by the handler and sent as response. Means the error is sent from the driver.

Did you got working results using the browser ?

 

[Gearcam]

yes if file not open on server then browser works

Error only if database open on server

 

[Tron]

Hmm.
http://stackoverflow.com/questions/19777323/set-database-to-open-in-non-exclusive-mode

In words: your database should be opened in NON-EXCLUSIVE-MODE

 

[Gearcam]

that's how the backend database is set it works as many PC users are connected all day.

 

[sorex]

If I recall right you will also get errors when you get into table design mode since it locks out everything since you're (re-)designing things.

That's why mssql/mysql are way better alternatives but since it's a live db app already that's not an option.

 

[Tron]

... please bear in mind, that is easy to migrate access database tables into MSSQL (or the free Express version) .

And you can easily attach (link) the tables from MSSQL into access (which wasn´t designed to be multiuser/multitasking) if neccessary.

At least there should be a much better performance.

 

[Gearcam]

I have tried to do this but have some form issues in the PC front ends

maybe I have to do this first ?

I think this is the solution I am now moving some data to SQL Server to test
What's the best method for SQL Server database connection ?

Steve

 

[Tron]

... if the datasources of your access Database are a mssql tables, then all form and report features remain. Or ?

 

[Gearcam]

yes all tables in MS SQL 2012 server

 

[Gearcam]

I am going to use the JSON lib if I can get it to talk to the server

I have used it to talk to a database on a web based database with
HttpUtils2

 

[Tron]

In my ACCESS Handler Version and in the MSSQL Version I used horizontal tab (0x09) as field separator and Linefeed (0x0a) as Lineseparator.
But it is easy to change it into JSON or XML (Sax) format incl. Base64 encoding.

Similar to http://www.b4x.com/android/forum/threads/connect-android-to-ms-sql-server-tutorial.13166/#content
and
http://www.b4x.com/android/forum/threads/xml-parsing-with-the-xmlsax-library.6866/