Source language: Translate to:

i want to move up from access to sql

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

i want to move up from access to sql

Postby yanzco » Sat Mar 25, 2017 9:08 pm

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"

:)
yanzco
 
Posts: 178
Joined: Sun Jul 20, 2014 4:07 am

Re: i want to move up from access to sql

Postby virger » Sun Mar 26, 2017 8:18 am

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?
COSTA RICA
PURA VIDA
User avatar
virger
 
Posts: 524
Joined: Mon Sep 18, 2006 12:21 pm
Location: Costa Rica, America Central

Re: i want to move up from access to sql

Postby Gaev » Sun Mar 26, 2017 9:31 am

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
User avatar
Gaev
 
Posts: 3728
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: i want to move up from access to sql

Postby dpayer » Mon Mar 27, 2017 8:33 am

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.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1382
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: i want to move up from access to sql

Postby stu » Thu Mar 30, 2017 9:34 am

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.
User avatar
stu
 
Posts: 320
Joined: Wed Aug 07, 2013 11:37 am

Re: i want to move up from access to sql

Postby yanzco » Sun May 07, 2017 8:19 am

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..
yanzco
 
Posts: 178
Joined: Sun Jul 20, 2014 4:07 am

Re: i want to move up from access to sql

Postby yanzco » Sun May 07, 2017 10:09 am

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
yanzco
 
Posts: 178
Joined: Sun Jul 20, 2014 4:07 am

Re: i want to move up from access to sql

Postby HackinHoodLogicsTm » Mon May 08, 2017 4:06 am

@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.
I am proud to be a Neobooker!!!
www.neosoftware.com
Special Thanks To
Mr. David Riley
User avatar
HackinHoodLogicsTm
 
Posts: 120
Joined: Wed Aug 28, 2013 7:03 am
Location: Ghana

Re: i want to move up from access to sql

Postby yanzco » Mon May 08, 2017 5:26 am

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..
yanzco
 
Posts: 178
Joined: Sun Jul 20, 2014 4:07 am

Re: i want to move up from access to sql

Postby dpayer » Wed May 10, 2017 11:41 am

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
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1382
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: i want to move up from access to sql

Postby Neosoft Support » Wed May 10, 2017 5:46 pm

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5602
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: i want to move up from access to sql

Postby yanzco » Tue May 16, 2017 6:54 pm

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...
yanzco
 
Posts: 178
Joined: Sun Jul 20, 2014 4:07 am

Re: i want to move up from access to sql

Postby dpayer » Wed May 17, 2017 8:50 am

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.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1382
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron