database acces + sql + .txt

[dextrometofan]

Hello vbforums
I want to create a program with a database, but I got some problem
1. How Could I create new table in Acces via VB, i am planning to create "monthly attendance", or something like that
2. Could I use SQl instead Acces for my program, but I am completely have no idea how to do it, could someone give me e-book/link/lesson for this matter (I have learn most of basic command on SQL though but do not know how to connect it to vb)
3. my program use an encryption .txt file to save it login_data, I can read and add data on it, but how could I edit the file ?
in a txt file something like this

"userA","passA"
"userB","passB"

Any help will be appreciated, Thanks

note : sorry for my bad english, Iam suck on it

[akhileshbc]

1. How Could I create new table in Acces via VB, i am planning to create "monthly attendance", or something like that

You can use SQL statements to create tables in Access: http://msdn.microsoft.com/en-us/libr...#acfundsql_ddl

2. Could I use SQl instead Acces for my program, but I am completely have no idea how to do it, could someone give me e-book/link/lesson for this matter (I have learn most of basic command on SQL though but do not know how to connect it to vb)

You mean SQL server ?

Links:
* http://www.vbforums.com/showthread.php?t=337051#ado
* http://www.vbforums.com/showthread.p...7051#SQLServer

3. my program use an encryption .txt file to save it login_data, I can read and add data on it, but how could I edit the file ?
in a txt file something like this

Link: How can I edit a text file without opening it?

...

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

[dextrometofan]

You mean SQL server ?

thx,
I mean SQl without server, I want to use the program on stand alone PC without Network, maybe save the database on an .SQL file or something like that

[akhileshbc]

thx,
I mean without SQl without server, I want to use the program on stand alone PC without Network, maybe save the database on an .SQL file or something like that

I think, MS Access is good for that.

[si_the_geek]

SQL is not a database system, it is the language you use to communicate with databases (such as "SELECT * FROM tablename"), and can be used for almost any database system - including Access.

You can use the Express or Compact editions of SQL Server, as they do not need an actual server. There are also "lite" editions of MySQL and Oracle etc.

[dextrometofan]

O.o
I don't know about that,
Basiccaly, i want to save my login information on a database, but Acces doesn't seem right
I want to use a .txt file ,
for example :

"Jane whatever","12345" 
"Dome Axe","qwerty"
"Blablabla","zxcv"

how can I read, edit, add, remove, some part of it in most simple way

[akhileshbc]

O.o
I don't know about that,
Basiccaly, i want to save my login information on a database, but Acces doesn't seem right
I want to use a .txt file ,
for example :

"Jane whatever","12345" 
"Dome Axe","qwerty"
"Blablabla","zxcv"

how can I read, edit, add, remove, some part of it in most simple way

A good place to start: http://www.vbforums.com/showthread.php?t=348141#file

[dextrometofan]

Ok, thx
how about to setup a password on acces via vb
and how to open passworded Acces databases

[akhileshbc]

To set password for MS Access db: http://www.databasedev.co.uk/database_password.html

To open password protected db in VB6, include the password in the connection string:

Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= " & App.Path & "\database.mdb;Persist Security Info=False;" & _
      & "Jet OLEDB:Database Password=mypassword;"
    cn.Open
    Set rs = New ADODB.Recordset

...

[dextrometofan]

Is it possible to use For..Next to add data??

Private Sub add_Click()
Dim i As Integer
dbPribadi.AddNew
For Each Control In Me.Controls
On Error Resume Next
    If TypeOf Control Is ComboBox Then
        dbPribadi.Fields(10 - i) = Control.Text
        i = i + 1
    ElseIf TypeOf Control Is TextBox Then
        dbPribadi.Fields(10 - i) = Control.Text
        i = i + 1
    End If
Next Control
dbPribadi.Update
End Sub

it's miserably failed to add data on databases,
contained data in control.txt itself seems valid

i am planning to insert the function to a module, cuz i don't really want to write

dbPribadi.Fields(1) = Text1.tx

in each form (i am planning to write loots of form)
But if nobody know, i'll write it

[si_the_geek]

You desperately need to read the article What is wrong with using "On Error Resume Next"? from our Classic VB FAQs (in the FAQ forum). The way you have used it is ridiculous, and extremely counter-productive.


While it is possible to use a For loop to do what you want, it will take a lot of code to make it work properly and reliably - for example you need to take into account getting the right textbox linked to the right field, and ignore textboxes that aren't supposed to link to fields, etc.

It will probably be much less effort to write out a line for each control, even if it is somewhat repetitive.

[dextrometofan]

yes, I've read it and I know the risk,
I just want to know if there is other way
I just want to know any posible way
I AM noob, I am still learning and I want to satisfy my curiosity
although curiosity killed the cat
the code itself seems gives right result when I put it on MsgBox

[si_the_geek]

There are few situations where O.E.R.N. is good idea, and the code you showed is very definitely not one of them... using it like that isn't about "risk", it is about wanting to be bad at programming.

In almost any code there is a risk of failure... but with O.E.R.N. as you used it, the damage caused by that failure is not only far larger than it would have been (rather than seeing an error message and the program exit, you write bad data to the database), but also you are intentionally hiding all kinds of failures from yourself - which means that you could be doing damage for a long time before you even notice, and when you do find out you wont have any idea what caused it either.

Rather than pretending everything is OK, let VB tell you when you have made a mistake - and then work on fixing it.


Using a loop for this is not a wise move unless you are extremely confident that you can deal with the issues I mentioned before (and others I didn't). It would be far safer (and probably take less time) to write out a line of code for each control.

Over the years I have written thousands of forms that do this kind of thing, and have only used a loop like that for one project - and there is no way it would have been just one if it saved effort.

[dextrometofan]

OK, after a looong fight (read:night)
finally, I got the code without O.E.R.N

Public Sub addDB(rs As ADODB.Recordset, frm As Form, i As Integer, modif As Integer)
ReDim Data(i) As String
Dim n As Integer
For Each control In frm.Controls
    If TypeOf control Is ComboBox Then
        Data(i - n) = control.Text
        n = n + 1
        ElseIf TypeOf control Is TextBox Then
        Data(i - n) = control.Text
        n = n + 1
    End If
Next control
rs.AddNew
For n = 0 To i - 1
        rs.Fields(n) = Data(n + modif)
Next n
rs.Update
Set rs = Nothing
End Sub

i put it on a module and then call it on any needed form
modif used to syncronized betwen form-database
could anybody check the code please

[si_the_geek]

That is better, but you haven't dealt with the issues I mentioned.


For example, it rather hard to predict which field will be linked to which textbox.

Even after you have worked it out, you are heavily relying on the "For Each" returning the controls in a particular order, which may not always be the case.

If they are returned in a different order for any reason, you will write data to the wrong fields.

[dextrometofan]

Yes,
U totally true
I am heavily relying on modif to syncronized it
I must draw the "object" in right order
a single mistake means I must re-design the form

I got a problem again
do you know a comand to filter the database
I was trying something like this

"select*from tblData where ID = '" & combo1.text & "'"

but it is failed to get any data
recordcount = -1 or something like that
And yes, I already read the FAQ

And how to get the current index of in selected database >> I don't know How to say it in english
and total count of data in a database
and how to move selected database to "X"
can I use

rs.move X

Ps : wow!! I am totally suck in English though I got perfect score in National Exams on High School

[akhileshbc]

Try this (included some space):

"select * from tblData where ID = '" & combo1.text & "'"

For counting the records, try this:

rs.Open "SELECT Count(*) as TotalCount FROM tblData ", cn, adOpenForwardOnly, adLockReadOnly
Label1.Caption = CStr(rs.Fields("Totalcount")) '~~~ Displays total count in a LabelBox
rs.Close

...

[dextrometofan]

so..
we can't use something like

 .recordcount

like in Data or AdoDC
my author only taught us about data1 and Adodc1, but not adodb
I don't even know what is diferrent betwen them
somebody could give some information ??

[akhileshbc]

http://www.vbforums.com/showthread.php?t=526882

[si_the_geek]

my author only taught us about data1 and Adodc1, but not adodb
I don't even know what is diferrent betwen them
somebody could give some information ??

ADODB is ADO code.

ADODC is the ADO Control, and it has the same features as ADO code - but lots of the work is done for you, and hidden from you (so you cannot change the things it does badly, which is quite a lot).

Data1 is the DAO control - and just like DAO code it should not be used for new projects after 1997 (the help for VB6 is one of the many places that says so!).

Ps : wow!! I am totally suck in English though I got perfect score in National Exams on High School

You are far better than I would be in a different language!

[dextrometofan]

how to get Data from a range of date

Call opentable("select * from data_pribadi where Nama = '" & Nama & "'" and tanggal >= " & DateA  & " and tanggal <= " & DateB , dbPribadi)

it didn't work
Attachment 77572

[si_the_geek]

Rather than just posting things like "it didn't work", please give us details - such as the error message etc.


It seems like you have not put the values into the SQL correctly... so take a look at the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)

[dextrometofan]

it just didn't give any record, no error msg

from the link

  For most versions of Access [and other Jet databases] (others versions use ' as above)
Select * 
From myTable
Where dateField = #01/01/2006#

is it MM/dd/yyyy or dd/MM/yyyy

bytheway, what kind of datatype in acces to save "time"??
string or date??

[si_the_geek]

is it MM/dd/yyyy or dd/MM/yyyy

The FAQ article explains that.

bytheway, what kind of datatype in acces to save "time"??
string or date??

Date - which always stores Date and Time (so if you want both, you can use just one field).

[dextrometofan]

i want to know how to write a log everytime database changed, create a new log file every month/log deleted , Am I must write the code every

rs.update
rs.delete

or is there other way??


sometimes my acces file sudenlly gets so big, more than 2 MB for less than 20 record on 4 tables
and getting really sloww
when I open it on M acces, then klik database utilities > compact and repair databases, i'ts size reduced to 194KB, can we use something in vb to call this function??

[si_the_geek]

i want to know how to write a log everytime database changed, create a new log file every month/log deleted , Am I must write the code every

rs.update
rs.delete

or is there other way??

With file based databases, you need to do it after every Update etc - and it isn't entirely reliable (due to code mistakes, or changing records in the database directly, etc).

If you were using a server based database (such as SQL Server Express) you could create a trigger within the database to do it.

sometimes my acces file sudenlly gets so big, more than 2 MB for less than 20 record on 4 tables
and getting really sloww

That is one of the joys of a file-based database.

when I open it on M acces, then klik database utilities > compact and repair databases, i'ts size reduced to 194KB, can we use something in vb to call this function??

Yes, see the Access section of the DB FAQs.

[dextrometofan]

I've se this FAQ
but couldn't find how to fix
"invalid use of Null" or something like that
I'm gonna to explode.....................!

[akhileshbc]

Check this FAQ: Why do I get the error "Invalid use of Null" when using field values?

[dextrometofan]

thx,
Is it working for any data type ??
such as Date or Byte, will it work without give any futher problem such as wrong data type inserted
Ps: I've not tested it, my project is at home right now, and I don't have i-net connection there

[si_the_geek]

It works for every data type.

[dextrometofan]

thx, i've try it at home
I want to fill array using data from database
must I use Dim or reDim?

Private Sub Form_Load()
Dim i As Integer
Dim varA() As String
Call comboDB("select*from data_perusahaan", dbPerusahaan, cbPerusahaan)
    Periode.Value = autoval
    Per
Call opentable("select * from data", dbdata)
    dbData.MoveFirst
Lo:
    If Not (dbData.EOF = True) Then
    i = i + 1
    varA(i) = dbData.Fields(0)
    MsgBox varA(i)
       dbData.MoveNext
       GoTo Lo
    End If
dbData.Close
Debug.Print DateA & "  Load!!"
Debug.Print DateB
End Sub

err >> subscript out of range

[si_the_geek]

Why do you want it in an array?

That is usually a bad idea, and it is generally better (in terms of memory usage/speed/etc) to use the recordset instead.

If you do have a valid reason, this is how you would do it:

varA = dbData.GetRows

Note that you should always avoid GoTo, because it makes your code harder to read (and therefore more likely to have mistakes in it). In this case, this section of code:

Lo:
    If Not (dbData.EOF = True) Then
    i = i + 1
    varA(i) = dbData.Fields(0)
    MsgBox varA(i)
       dbData.MoveNext
       GoTo Lo
    End If

...should have been like this:

    Do While Not (dbData.EOF = True)
      i = i + 1
      varA(i) = dbData.Fields(0)
      MsgBox varA(i)
      dbData.MoveNext
    Loop

[dextrometofan]

i don't know how to use

.GetRows

-----

    Do While Not (dbData.EOF = True)
      i = i + 1
      varA(i) = dbData.Fields(0)
      MsgBox varA(i)
      dbData.MoveNext
Loop

i'll try it

-------------------

select * from tb_Attendance where ID = '" & dbData.Fields(0) & "' and Date => #" & DateA & "# and Date <= #" & DateB & "#"

syntax error (missing operator)

where is the mistakes