-
database acces + sql + .txt
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
Quote:
"userA","passA"
"userB","passB"
Any help will be appreciated, Thanks
note : sorry for my bad english, Iam suck on it :blush:
-
Re: database acces + sql + .txt
Quote:
Originally Posted by
dextrometofan
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
Quote:
Originally Posted by
dextrometofan
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
Quote:
Originally Posted by
dextrometofan
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?
...:wave:
-
Re: database acces + sql + .txt
Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)
-
Re: database acces + sql + .txt
Quote:
Originally Posted by
akhileshbc
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
-
Re: database acces + sql + .txt
Quote:
Originally Posted by
dextrometofan
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.
-
Re: database acces + sql + .txt
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.
-
Re: database acces + sql + .txt
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 :
Code:
"Jane whatever","12345"
"Dome Axe","qwerty"
"Blablabla","zxcv"
how can I read, edit, add, remove, some part of it in most simple way
-
Re: database acces + sql + .txt
Quote:
Originally Posted by
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 :
Code:
"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 :wave:
-
Re: database acces + sql + .txt
Ok, thx
how about to setup a password on acces via vb
and how to open passworded Acces databases
-
Re: database acces + sql + .txt
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:
Code:
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
...:wave:
-
Re: database acces + sql + .txt
Is it possible to use For..Next to add data??
Code:
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
Code:
dbPribadi.Fields(1) = Text1.tx
in each form (i am planning to write loots of form)
But if nobody know, i'll write it http://www.indowebster.web.id/images...s/onion-84.gif
-
Re: database acces + sql + .txt
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.
-
Re: database acces + sql + .txt
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 http://www.indowebster.web.id/images...ovea/hahai.gif
the code itself seems gives right result when I put it on MsgBox
-
Re: database acces + sql + .txt
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.
-
Re: database acces + sql + .txt
OK, after a looong fight (read:night)
finally, I got the code without O.E.R.N
Code:
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
-
Re: database acces + sql + .txt
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.
-
Re: database acces + sql + .txt
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
Code:
"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
Ps : wow!! I am totally suck in English though I got perfect score in National Exams on High School
-
Re: database acces + sql + .txt
Try this (included some space):
Code:
"select * from tblData where ID = '" & combo1.text & "'"
For counting the records, try this:
Code:
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
...:wave:
-
Re: database acces + sql + .txt
so..
we can't use something like
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 ??
-
Re: database acces + sql + .txt
-
Re: database acces + sql + .txt
Quote:
Originally Posted by
dextrometofan
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!).
Quote:
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!
-
Re: database acces + sql + .txt
how to get Data from a range of date
Code:
Call opentable("select * from data_pribadi where Nama = '" & Nama & "'" and tanggal >= " & DateA & " and tanggal <= " & DateB , dbPribadi)
it didn't work
Attachment 77572
-
Re: database acces + sql + .txt
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)
-
Re: database acces + sql + .txt
it just didn't give any record, no error msg
from the link
Code:
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??
-
Re: database acces + sql + .txt
Quote:
Originally Posted by
dextrometofan
is it MM/dd/yyyy or dd/MM/yyyy
The FAQ article explains that.
Quote:
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).
-
Re: database acces + sql + .txt
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
Code:
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??
-
Re: database acces + sql + .txt
Quote:
Originally Posted by
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
Code:
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.
Quote:
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.
Quote:
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.
-
Re: database acces + sql + .txt
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.....................!
-
Re: database acces + sql + .txt
-
Re: database acces + sql + .txt
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
-
Re: database acces + sql + .txt
It works for every data type.
-
Re: database acces + sql + .txt
thx, i've try it at home
I want to fill array using data from database
must I use Dim or reDim?
vb Code:
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
-
Re: database acces + sql + .txt
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:
Code:
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:
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:
Code:
Do While Not (dbData.EOF = True)
i = i + 1
varA(i) = dbData.Fields(0)
MsgBox varA(i)
dbData.MoveNext
Loop
-
Re: database acces + sql + .txt
i don't know how to use
-----
Code:
Do While Not (dbData.EOF = True)
i = i + 1
varA(i) = dbData.Fields(0)
MsgBox varA(i)
dbData.MoveNext
Loop
i'll try it
-------------------
Code:
select * from tb_Attendance where ID = '" & dbData.Fields(0) & "' and Date => #" & DateA & "# and Date <= #" & DateB & "#"
syntax error (missing operator)
where is the mistakes