Page 1 of 1

i want to move up from access to sql

PostPosted: Sat Mar 25, 2017 9:08 pm
by yanzco
i need help from you guys using dbpro for sql...


i know that most of the commands in dbpro can be used for access and sql..
and since most of people recommends using sql more than access..

i want to move up...

but i dont know how to, can someone help me , or give guides on steps...
to making sql tables / databases...

reason i got in to access is because, when opening microsoft access, i can design tables, and fields on the go. then use neobook dbpro to use it...
but for sql, i dont know how..

do i need to install some odbc driver or other stuff?..

> complete beginner of sql..




how is sqlite in dbpro?

edit: i have been following https://www.tutorialspoint.com/sqlite/s ... _table.htm
but i cant seem to get neobook dbpro to open tables inside the .db

yet when i try to view it with GUI DB Browser for sqlite
http://sqlitebrowser.org/

the tables i created exists..

yet in dbpro, it shows blank..
why? :(



edit: i have tried to use open database in dbpro with
dbpOpenDatabase "TESTDB" "Data Source=[PubDir]test.db;Provider=MSDASQL.1;Persist Security Info=False;Data Source=SQLite Datasource"
( i dont know if this is the right way)

and use dbpGetTableNames "TESTDB" "," "[list]"

it shows blank...

edit: this one worked.. and i got the tables to show up..
dbpOpenDatabase "TESTDB" "Provider=MSDASQL.1;Driver=SQLite3 ODBC Driver;Database=[PubDir]TESTDB.db"

:)

Re: i want to move up from access to sql

PostPosted: Sun Mar 26, 2017 8:18 am
by virger
No entiendo bien cual es la duda, la ayuda de dbpro nos facilita la creacion de bases y sus tablas, asi como "llenar" estas con informacion ya existente (proveniente de otras bases) dbpCreateAccessDatabase, dbpCreateTable, dbpImportFromCSV...
Podfrias explicarlo en español ?

I do not understand well what is the doubt, the help of dbpro facilitates the creation of bases and their tables, as well as "fill" these with existing information (from other bases) dbpCreateAccessDatabase, dbpCreateTable, dbpImportFromCSV ...
Could you explain it in Spanish?

Re: i want to move up from access to sql

PostPosted: Sun Mar 26, 2017 9:31 am
by Gaev
yanzco:

Further to virger's comment ...
I do not understand well what is the doubt, the help of dbpro facilitates the creation of bases and their tables, as well as "fill" these with existing information (from other bases) dbpCreateAccessDatabase, dbpCreateTable, dbpImportFromCSV ...

... you might take a look at a general purpose utility (AccessDBU) that I built many years ago ...
AccessDBU© is a utility program that allows developers to rapidly create Access databases and load data for testing their applications ... and can also be used in the investigation and/or repair of messed up content.

It enables you to ...

- Create and Delete Access Databases
- Add, Modify and Drop Tables within the Databases
- Add, Change, Remove, Import and Export records in the Tables

... all without writing a single line of code.

... it can be downloaded from here ... http://www.scriptedlogic.com/zzold/AccessDBU.htm

Re: i want to move up from access to sql

PostPosted: Mon Mar 27, 2017 8:33 am
by dpayer
I moved from Access to MySQL. It has some advantages:

1) Free
2) ready to use with a WAMP (Windows/Apache/MySQL/PHP) package available to download/install HERE
3) multi user - no problem across a network
4) easy quick management tool built in via PHPMyAdmin (create databases/tables/import/export, etc)

While there are a few syntax differences from Access, the web is full of answers for MySQL which is used on the majority of websites.

I have created an inventory application with DBPro and MySQL that has made significant differences in my workflow.

Re: i want to move up from access to sql

PostPosted: Thu Mar 30, 2017 9:34 am
by stu
Good call... Access sucks hard, anyone else saying different is wrong.

My personal preference is MS SQL Server. Solid, scalable, consistent, enterprise grade database engine with a massive community.

Express version is free and Management Studio its just dump-proof... its supper simple to get up developing databases with it...

I only built one application using DPPRO and SQL Server, its still up running no problems on a few tills (DVD Rentals management)... I make use of dbpExecSQL for the entire code, you just cant rely on the other commands, sometimes they just go spastic with different database engines, its a lot better to build your queries to make sure you're gonna get the desired result. Since then I moved into C# so thats my one experience with dppro and an actual database engine.

Re: i want to move up from access to sql

PostPosted: Sun May 07, 2017 8:19 am
by yanzco
hello... i somehow stopped for a while due to some critical business problems...
now that i am back trying to learn sqlite with neobook..

i somehow cant open the test sqlite database file i created..

( note: i recently reformatted my pc.. )

i am sure some database driver is missing...

but i dont know which and what to find...

this is the screenshot..

Image


i used
Code: Select all
dbpOpenDatabase "TESTSQLDB" "Provider=MSDASQL.1;Driver=SQLite3 ODBC Driver;Database=D:\EVA Files\Desktop\TESTDB.db"



at the open db button

i followed the post i created above that was successful in opening sqlite db...
but now its not working..

Re: i want to move up from access to sql

PostPosted: Sun May 07, 2017 10:09 am
by yanzco
somehow it worked now after installing sqliteodbc.exe

but i cant open a table with dbpOpenTable..
it says cant open table..

help...

for ex: i want to open a table named "Table1"
and show the grid to rect1

Re: i want to move up from access to sql

PostPosted: Mon May 08, 2017 4:06 am
by HackinHoodLogicsTm
@yanzco

To connect DBPPro with SQL. You need to setup SQL Driver through
Control Panel>Administrative Tools>ODBC Data Sources>Then from the ODBC Source Screen Click on
User DSN or System DSN

Where User DSN only install the driver to just the Logged in user whilst the SystemDSN install the drivers for all users on the system.

So Click on User DSN>Add
From the Create New Data Source Select from the List SQL Server and Proceed from there....

As far as i know you must have MS SQL Server installed.
You can install any version. I used 2008, 2012 and 2017 and it all worked.

Re: i want to move up from access to sql

PostPosted: Mon May 08, 2017 5:26 am
by yanzco
it seems to work now after i installed sqliteodbc.exe

it says the database is connected..
just having problem opening a table using the normal commands from neobook..
it seems i have to use dbpEXECSQL

but i have no idea how to use the advance commands..

using the normal dbpOpenTable gives me an error cannot open table..

Re: i want to move up from access to sql

PostPosted: Wed May 10, 2017 11:41 am
by dpayer
yanzco wrote:it seems to work now after i installed sqliteodbc.exe

it says the database is connected..
just having problem opening a table using the normal commands from neobook..
it seems i have to use dbpEXECSQL

but i have no idea how to use the advance commands..

using the normal dbpOpenTable gives me an error cannot open table..


Time to learn new things!

https://www.w3schools.com/sql/DEfaULT.asP

Re: i want to move up from access to sql

PostPosted: Wed May 10, 2017 5:46 pm
by Neosoft Support
dbpOpenTable should work with mySQL. You might try using dbpGetTableNames and see if it returns a list of table names in the database. If it does, then make sure you're using the same exact table names with dbpOpenTable.

Re: i want to move up from access to sql

PostPosted: Tue May 16, 2017 6:54 pm
by yanzco
im really having a bad time with this..
can someone help me / teach me.. the basics of creating a mysql or sqlite database
and the opening tables, adding, editing, querying commands?...

for example..
if i have a big database.. and i want to get a specific record data on it.. what would be the command using dbpExecSQL

or like, if i want to show a table in a rect.. just like showgrid..
how can i do that with dbpExecSQL using mysql or sqlite...

Re: i want to move up from access to sql

PostPosted: Wed May 17, 2017 8:50 am
by dpayer
yanzco wrote:im really having a bad time with this..
can someone help me / teach me.. the basics of creating a mysql or sqlite database
and the opening tables, adding, editing, querying commands?...

for example..
if i have a big database.. and i want to get a specific record data on it.. what would be the command using dbpExecSQL

or like, if i want to show a table in a rect.. just like showgrid..
how can i do that with dbpExecSQL using mysql or sqlite...


To create your initial database, some of the other tools that often come with the db server can be helpful. For example, if you install a WAMP server (Windows / Apache webserver / MySQL / PHP) - do a google for wamp for download - you will have included and already installed: PHPMyAdmin. This is a web tool to create databases and tables, etc. It is easier to start with a database and tables and then add content IMO. There are similar tools available for sqlite I know.

To find a specific record, you have to Open the DB , open the table, then do a SQL "SELECT" command (w3schools is a very helpful place for syntax on sql commands). Typically it is like this:

SELECT * FROM tablename WHERE 'columnname=desiredvalue'

This will choose all the column values of all records that have the desired value in that column. It is also possible to have the select choose only certain columns for display. Again, read the tutorial at w3schools.