How To Call Records From Ms-access Database To Msflexgrid
Dear all,
I want to call data from ms-access database to msflexgrid, thats have several record in the 1 treat no. or same criteria, but only 2 record will show in msflexgrid, the code is :
Xrow = I + 1
Data1.Recordset.FindFirst "Treatno = '" & Me.TxtTreatNo & "'"
If Data1.Recordset!treatno = TxtTreatNo.Text Then
For I = 0 To Xrow
MSFDetHT.TextMatrix(I, 0) = Data1.Recordset!productID
MSFDetHT.TextMatrix(I, 1) = Data1.Recordset!NameSpec
MSFDetHT.TextMatrix(I, 2) = Data1.Recordset!Unit
Data1.Recordset.FindNext "Treatno = '" & Me.TxtTreatNo & "'"
Next I
End If
Please help me to check the problem in the code, and I usually blank the text box with code Text1.text = "" and how to blank the masked edit box.
thanks and good day.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Textfile Records And Access Database Records Matching
how do i do matching whith using looping on textfile records and database records? if the records in textfile and access database are not match then it will have a output on a.txt and the output is retreive from textfile
the bugs is, there are no output (it suppose to have output)
please help ... thanx
VB Code:
Do While Not RS.EOF strFileNoDB = CatchNull(Trim(RS("FILE_NO").Value), "Null") strBenCdDB = CatchNull(Trim(RS("BEN_CD").Value), "Null") strBenNameDB = CatchNull(UCase(Trim(RS("BEN_NAME").Value)), "Null") strBenICPassDB = CatchNull(Trim(RS("IC_PASSP_B").Value), "Null") strGuardNameDB = CatchNull(UCase(Trim(RS("GUARD_NAME").Value)), "Null") strGuardICPassDB = CatchNull(Trim(RS("IC_PASSP_N").Value), "Null") strBenICPassDBHy = Replace(strBenICPassDB, "-", "") strGuardICPassDBHy = Replace(strGuardICPassDB, "-", "") strFileOutputDB = strFileNoDB & strBenCdDB & strBenNameDB & strBenICPassDBHy & strGuardNameDB & strGuardICPassDBHy If Left(ARBFile, 2) = "01" Then strTxnYear = Mid(ARBFile, 17, 4) strTxnMonth = Mid(ARBFile, 15, 2) strTxnYMJ = strTxnYear & strTxnMonth End If SeqNum = SeqNum + 1 strSeqNum = CStr(SeqNum) For x = 0 To aIndex If Left(ARBFiles(x), 2) = "02" Then If Len(strSeqNum) = 1 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 2 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 3 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 4 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 5 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 6 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 7 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 8 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 9 Then SeqNumJ = strSeqNum & " " ElseIf Len(strSeqNum) = 10 Then SeqNumJ = strSeqNum End If strFileNo = Mid(ARBFiles(x), 51, 8) strTxnStatus = "N" strBenCd = Mid(ARBFiles(x), 59, 2) If Len(strBenCd) = 1 Then strBenCdJ = strBenCd & " " ElseIf Len(strBenCd) = 2 Then strBenCdJ = strBenCd & " " End If strBenCdC = Trim(strBenCd) strBenName = UCase(Mid(ARBFiles(x), 61, 60)) strBenNameJ = Trim(strBenName) strBenICPass = Trim(Mid(ARBFiles(x), 15, 12)) strBenICPassJ = strBenICPass & " " strGuardName = UCase(Mid(ARBFiles(x), 165, 80)) strGuardNameJ = Trim(strGuardName) strGuardICPass = Trim(Mid(ARBFiles(x), 141, 12)) strGuardICPassJ = strGuardICPass & " " strAmount = Mid(ARBFiles(x), 271, 13) strAmountCent = Right(strAmount, 2) strAmountDecimal = "." & strAmountCent If Left(strAmount, 11) = "00000000000" Then strAmountRinggetFilterJ = "0" strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 10) = "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 3) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 1) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 9) = "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 4) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 2) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 8) = "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 5) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 3) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 7) = "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 6) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 4) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 6) = "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 7) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 5) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 5) = "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 8) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 6) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 4) = "0000" And Left(strAmount, 5) <> "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 9) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 7) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 3) = "000" And Left(strAmount, 4) <> "0000" And Left(strAmount, 5) <> "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 10) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 8) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 2) = "00" And Left(strAmount, 3) <> "000" And Left(strAmount, 4) <> "0000" And Left(strAmount, 5) <> "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 11) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 9) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " ElseIf Left(strAmount, 1) = "0" And Left(strAmount, 2) <> "00" And Left(strAmount, 3) <> "000" And Left(strAmount, 4) <> "0000" And Left(strAmount, 5) <> "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then strAmountRinggetFilter = Right(strAmount, 12) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 10) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " Else strAmountRinggetFilter = Right(strAmount, 13) strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 11) strAmountRinggetFilterJ = strAmountRinggetFilter1 strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal strAmountJ = strAmountCombine & " " End If strFileOutputJ = strFileNo & strBenCdC & strBenNameJ & strBenICPass & strGuardNameJ & strGuardICPass If strFileOutputDB <> strFileOutputJ Then strARBFileContent = SeqNumJ & strFileNo & strTxnStatus & strBenCdJ & strBenName & strBenICPassJ & strAmountJ & strTxnYMJ Print #2, strARBFileContent ElseIf strFileOutputDB = strFileOutputJ Then Exit Do End If End If Next x RS.MoveNext Loop
Lock Records In Access Database Access :D
I'm working in an accounting software (if I'm translating OK from spanish )
Situation:
Access 2000 database.
One user is editing or deleting a record in one table.
Another user want to access the same record. I must lock the record for editing or dleting, but not for consulting, but in this case I have to notify the consultant this record is being editing by other user.
I don't know how to do this, thanks.
SQl To Access Database Records
I have an MsAccess DB created with Visual Data Manager.
It's a simple Address Book, I am trying to retrieve records based on a SQL. I have a TextBox on the form, I want to enter the first name of an address and have it find the record.
Here is my Sub cmdFind _Click ()Procedure
frmViewAddress.AdoAddress.RecordSet =
"select * From tblAddress Where fldName" Like "txtFind*"
I get a compile error message saying "Expected AddNew, or Delete,or Update, or SELECT."
If I remove the Quotes before select and after fldName, the error message (with select highlighted) says "expected Procedure". What am I doing wrong?
The address name field in the database has this format, "John and Jane Doe"
The RecordSource Property in the Ado Control uses this SQL. select * From tblViewAddress
and it works fine.
any help will be a blessing. Thank You
Gary Swartz
gary35.wa@netzero.net
Adding Records To Access Database
I am trying to enter 5 columns into a database from 5 text boxes, then it will update a mshflexgrid on another form in run time. I figured out how to display the grid ok, but can't figure out how to update the database from the 5 textboxes. It doesn't seem to do anything when I click the Add cmdbutton on frmEnter. Attached is the project, this is for a school project.
Thanks,
Duane
Selecting Records In An Access Database Thru VB6
Ok I am trying to return some records from an Access database. For some odd reason this will not work. When I try to just return the BillToID it works, but not when I try to add the date of the invoice to print. Here is the SUB I am using, and the database layout.
Code:
Invoices
BillToID = Long Integer
DateOfInvoice = Date/Time
Code:
Sub PrintDaysInvoice(BillToID, BillDate)
'MsgBox "This feature is not yet working", vbInformation, "Invoices"
'Exit Sub
On Error GoTo ErrorPrintTodaysInvoice
MsgBox "BillToID-" & BillToID & vbCrLf & "BillDate" & BillDate
'Exit Sub
'Dim strFormLoadNumber
Dim A
Dim acNormal
If strDBOpen = 0 Then
OpenDatabase
End If
strSql = "SELECT LoadNumber"
strSql = strSql & " FROM Invoices"
strSql = strSql & " WHERE BillToID=" & BillToID
strSql = strSql & " AND DateOfInvoice=#" & BillDate & "#"
Set rs = my_Conn.Execute(strSql)
If rs.EOF Or rs.BOF Then
MsgBox "Can't find this invoice to print.", vbCritical, "Invoices"
Else
Do Until rs.EOF Or rs.BOF
strMessage = strMessage & vbCrLf & "LoadNumber-" & rs("LoadNumber")
'PrintInvoice rs("LoadNumber")
rs.MoveNext
Loop
MsgBox strMessage
End If
rs.Close
Set rs = Nothing
CloseDatabase
Exit Sub
ErrorPrintTodaysInvoice:
MsgBox Err.Description, vbCritical, "Invoices"
End Sub
Writting Records To Database (Access)
Need help on writting records to an Access database through vb code!
I have 3 variables with user input such as:
db is Customer
varCustomer
varAddress
varCity
Adding New Records To Access Database
Please HELP! I am using the following code to create new records into an Access database and for some reason the .movelast command suddenly doesn't move to the last record (at CRnum = 0115, where last record is supposed to be 0116). IT then tries to make a duplicate 0116.01 record causing an error. It seemed to have worked for the previous records. Thanks in advance.
Code:
Private Sub NewCRNumber_Click()
Dim mydatabase As Database
Dim myrecordset, As Recordset
Dim crnum As String
Set mydatabase = OpenDatabase("q:chemteamchem team databaseChem Team Database.mdb")
Set myrecordset = mydatabase.OpenRecordset("Assessments", dbOpenTable)
With myrecordset
If .RecordCount <> 0 Then
.MoveLast
crnum = Mid(.Fields("CR Number").Value, 1, 4)
crnum = crnum + 1
If Len(crnum) = 1 Then
crnum = "000" & crnum
ElseIf Len(crnum) = 2 Then
crnum = "00" & crnum
ElseIf Len(crnum) = 3 Then
crnum = "0" & crnum
End If
Else
' put in dialog box to enter first cr number
crnum = "0001"
End If
.AddNew
.Fields("CR Number").Value = crnum & ".01"
CRNumber.Text = .Fields("CR Number").Value
CRNumber.AddItem .Fields("CR Number").Value
.Update
.Close
End With
Call clearform
mydatabase.Close
End Sub
Printing All Records From An Access Database
Hiya
THere seems to be a lot of questions regarding links between access and vb.
I have looked through various different posts, but I think what i have seen may be much more than I need for my program.
I am linking to an access database - Stock via a data control and I need to write some code in vb to print out the records I have in my access database.
I am nore sure the best way to go about this. Can I use a common dialog box for this or should I be opening the recordset and somehow printing everything off....
Can anyone get me started with this?
Thanks
Tracey
Inserting Records Into An Access Database
Hi,
I'm very simply trying to insert records into an Access DB using ADO.NET. I've tried the 2 following ways and both give me an "oledb.oledbexception" but no othewr info.
The 1st method uses a command and executenonquery() method. The second uses a data adapter.
Method 1
Code:
Dim conn As New OleDb.OleDbConnection()
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:RichardAccount_ManagerAccount_Manager.mdb"
conn.Open()
Debug.WriteLine("conn state:" & conn.State)
Dim comm As New OleDb.OleDbCommand()
comm.Connection = conn
Debug.WriteLine(comm.Connection)
comm.CommandText = "insert into Employee(name,position,status) values('name','position','status')"
Debug.WriteLine(comm.CommandText)
Try
comm.ExecuteNonQuery()
conn.Close()
Catch ex As System.Exception
Debug.WriteLine(ex.Message)
Debug.WriteLine(ex.Source)
Debug.WriteLine(ex.StackTrace)
End Try
Method 2
Code:
Dim conn As New OleDb.OleDbConnection()
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:RichardAccount_ManagerAccount_Manager.mdb"
conn.Open()
Debug.WriteLine("conn state:" & conn.State)
Dim sql As String
sql = "select * from employee"
Dim da As New OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet()
da.Fill(ds, "test")
Dim dt As DataTable
dt = ds.Tables("test")
Dim commB As New OleDb.OleDbCommandBuilder(da)
da.InsertCommand = commB.GetInsertCommand()
Dim dr As DataRow
dr = dt.NewRow
dr.Item("name") = "name"
dr.Item("position") = "position"
dr.Item("status") = "status"
dt.Rows.Add(dr)
da.Update(ds, "test")
End Sub
Ramdon Access To Database Records
I am trying to randomly access to records in my database but continue to get errors when BOF or EOF... I know I almost get it but... What am I doing wrong? This has been driving me nuts for days now... the sample code follows.
Private Sub Exercises_Click()
Dim questions As Integer
Dim lastrec As Integer
Dim recordSelected As Integer
Dim counter As Integer
Dim total As Integer
questions = InputBox("Please indicate number of questions: ", "Number of questions")
lastrec = InputBox("Please indicate number of records to include in exercise. Enter for all: ", "Numbers of records", 0)
If lastrec = 0 Then
lastrec = datPrimaryRS.Recordset.RecordCount
End If
total = questions
For counter = 1 To total
Randomize
recordSelected = Int((lastrec + 2) * Rnd + 1)
datPrimaryRS.Recordset.Move recordSelected
Next counter
End Sub
Thanks a lot...
Demian
Moving Records To Another Access Database
hi
i currently have an access database and an asp interface to update and read it from the web. However, due to the nature of the information, it has the potential to grow quite large quite quickly.
I was wondering how to move records from one Access database to another one using ASP. I don't really know where to start with this.
thanks.
Delete Records From Access Database Via VB.NET
Please help,
I'm trying to create a little app to archive data out of an Access Db. I've created a DSN that I can use MSQuery to connect to the mdb with and select/delete but when I try to connect from inside the .NET app it doesn't delete.
Here's the code I've got. Plz excuse, I am very new to VB programming.
Private Sub sArchive()
dt = dtpAchDate.Text
Dim sql1 As Object
sql1 = "delete from tblInvoice where InvDate < " & dt
Dim td
Dim cn1 As ADODB.Connection
cn1 = New ADODB.Connection
cn1.Open("DSN=LCAdata")
cn1.Execute(sql1)
cn1.Close()
cn1 = Nothing
End Sub
I'm getting dt from a "date/time picker" and the sql1 = delete from tblInvoice where InvDate < 1/1/2000 in the watch window. It is the sql1 watch that I can copy/paste into MSQuery and run the delete fine.
I've tried changing the Dim sql1 as Object to String, there is no difference, although I believe it should be String shouldn't it? Once past this little bump, I have a few more questions like, how to list all the tables in an Access db and run the same query against each of them, like I mentioned, I'm trying to create an archiving app (that the creator of the Access db should have created in the first place) if I'm on the wrong path, please suggest, I am very much open to suggestions.
Thanks for any assistance anyone can provide. If this is a repeat thread again, I apologize, I did search and didn't find anything close enough to answer my problem.
Thanks again.
Deleting Records In An Access Database From VB
Hello,
I have a database created in access 2000 and i have created froms in vb to display the records from the access tables.
This all works well for adding records and viewing/editing.
How do i go about deleting records? so i can have a command button and when pressed will delete all the fields of the current record.
Thanks for any help given
- Chris
Edited by - Shandy on 6/1/2004 11:24:03 PM
Counting Records In A MS Access Database
Okay, I am somewhat familiar with VB... still learning.
My problem is that I am trying to write a program in VB6 that quickly does some statistics for me from an Access database so I don't have to run 36 different queries and look at each of them.
Basically, when I run this program, I want it to perform all 36 of these queries, count how many records are in each of them, and put each count in a separate textbox on my form. However, I have no idea how to do this in VB. I've searched numerous tutorials and none of them are telling me how to create a count of records or a query of a database for that matter. Please... any help would be VERY much appreciated. Meanwhile, I will continue trying to learn more about Access and connecting it to VB.
Adding Records In Access Database
Hey Gurus!
Can anyone help me how to add records in Access database?
In my program,I have a table named "tblClient" and 3 fields namely: Name,Address & Contact Number. What I want to achieve is that in my main form wherein I have 3 text boxes namely txtName,txtAddr & txtContactNo after having entered text on them will be added to my access database named "clientlist.mdb" when I click on the cmdAdd button.
Hope you can help me ASAP. thanx!
glenford@secomp-intl.com
Loading Access Database Into MSFlexgrid
Hello all,
i loading table form Access database into my MSFlexgrid1. I use coding as below to load selected fields using query table in Access. My coding seem not woking. i want to select data to be view in MSFlexgrid by selecting it by "batchno" from table "combine". Attached is my form for all of you see clear picture.
Please help me....
Code:
Private Sub cmdView_Click()
Dim strSearchString As String
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
MSFlexGrid1.Visible = False
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "BVCpbschm.mdb;Persist Security Info=False"
rs.Open "SELECT * FROM combain where batchno Like '" & strSearchString & "%' Order By mipid"
rs.MoveFirst
MSFlexGrid1.FormatString = "|BATCH NO|PKG|MIPID|MEMBERID |MEMBERSHIP |MEMBER NAME |DATE JOIN |NET PRICE |PREMINUM "
MSFlexGrid1.Rows = rs.RecordCount + 1
MSFlexGrid1.Cols = rs.Fields.Count + 1
MSFlexGrid1.RowSel = MSFlexGrid1.Rows - 1
MSFlexGrid1.ColSel = MSFlexGrid1.Cols - 1
MSFlexGrid1.Clip = rs.GetString(adClipString, -1, Chr(9), Chr(13), vbNullString)
MSFlexGrid1.Visible = True
Set rs = Nothing
Set db = Nothing
End Sub
Display Access Database At Msflexgrid
hello, how to connect the ado access database display at msflexgrid?
for example:
EmployeeName | EmployeeNo |Deparment|
User1 1 ADMIN
User2 2 ADMIN
User3 3 IT
this is my code:
Code:
Private Sub ConnectToDB()
Dim Sql As String
Dim cn As adodb.connection 'error message appear this line user defined type not found
Dim rs As adodb.Recordset
Set cn = New adodb.connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "overtime.mdb;" & "Persist Security Info=False"
Set rs = New Recordset
strsql = "select * from Employee"
rs.Open Sql, cn, adOpenDynamic, adLockOptimistic
rs.Close
End Sub
Private Sub Form_Load()
Dim i As Long
ConnectToDB
With MSFlexGrid1
.Redraw = False
.Rows = 1
.FormatString = "EmployeeName|EmployeeNo|Designation|Department|Shift"
For i = 1 To 10
.AddItem i
Next
For i = 0 To .Cols - 1
.ColWidth(i) = 1550
Next
For i = 1 To .Rows - 1
.Row = i: .RowSel = i
.Col = CheckBoxColIndex: .ColSel = CheckBoxColIndex
Set .CellPicture = IIf(.TextMatrix(i, CheckBoxColIndex) = "1", imgChecked.Picture, imgUnchecked.Picture)
.RowHeight(i) = ScaleY(imgChecked.Height, ScaleMode, vbTwips) + 15
Next
.Row = 0: .RowSel = 0
.Col = 0: .ColSel = 0
.Redraw = True
.Refresh
End With
End Sub
thanks for help!
data display is same as this.
Search Access Database From MSFlexgrid
Hey there all.
I have been searching the forum for hours and have had no luck with this query.
I currently have a MSFlexgrid on a form that is populated with a few colums from an access database when i do a search. I am able to select the grid by row.
The colums on the grid are: line_id(primary), customer_id, barcode, description.
What I have been trying to do is when i double click a row I would like to open another form to show me more information in that particular record in a database.
I am using ADO and am about to start pulling me hair out.
Thankyou to anyone that can help me with this
John
Display Access Database At Msflexgrid
hello, how to connect the ado access database display at msflexgrid?
for example:
EmployeeName / EmployeeNo /Deparment/
User1 1 ADMIN
User2 2 ADMIN
User3 3 IT
this is my code:
Code:Private Sub ConnectToDB()
Dim Sql As String
Dim cn As adodb.connection'error message appear this line user defined type not found
Dim rs As adodb.Recordset
Set cn = New adodb.connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "overtime.mdb;" & "Persist Security Info=False"
Set rs = New Recordset
strsql = "select * from Employee"
rs.Open Sql, cn, adOpenDynamic, adLockOptimistic
rs.Close
End Sub
Private Sub Form_Load()
Dim i As Long
ConnectToDB
With MSFlexGrid1
.Redraw = False
.Rows = 1
.FormatString = "EmployeeName/EmployeeNo/Designation/Department/Shift"
For i = 1 To 10
.AddItem i
Next
For i = 0 To .Cols - 1
.ColWidth(i) = 1550
Next
For i = 1 To .Rows - 1
.Row = i: .RowSel = i
.Col = CheckBoxColIndex: .ColSel = CheckBoxColIndex
Set .CellPicture = IIf(.TextMatrix(i, CheckBoxColIndex) = "1", imgChecked.Picture, imgUnchecked.Picture)
.RowHeight(i) = ScaleY(imgChecked.Height, ScaleMode, vbTwips) + 15
Next
.Row = 0: .RowSel = 0
.Col = 0: .ColSel = 0
.Redraw = True
.Refresh
End With
End Sub
thanks for help!
this result of data display is similiar as the image file.
Display Access Database At Msflexgrid
hello, how to connect the ado access database display at msflexgrid?
for example:
EmployeeName / EmployeeNo /Deparment/
User1 1 ADMIN
User2 2 ADMIN
User3 3 IT
this is my code:
Code:Private Sub ConnectToDB()
Dim Sql As String
Dim cn As adodb.connection'error message appear this line user defined type not found
Dim rs As adodb.Recordset
Set cn = New adodb.connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "overtime.mdb;" & "Persist Security Info=False"
Set rs = New Recordset
strsql = "select * from Employee"
rs.Open Sql, cn, adOpenDynamic, adLockOptimistic
rs.Close
End Sub
Private Sub Form_Load()
Dim i As Long
ConnectToDB
With MSFlexGrid1
.Redraw = False
.Rows = 1
.FormatString = "EmployeeName/EmployeeNo/Designation/Department/Shift"
For i = 1 To 10
.AddItem i
Next
For i = 0 To .Cols - 1
.ColWidth(i) = 1550
Next
For i = 1 To .Rows - 1
.Row = i: .RowSel = i
.Col = CheckBoxColIndex: .ColSel = CheckBoxColIndex
Set .CellPicture = IIf(.TextMatrix(i, CheckBoxColIndex) = "1", imgChecked.Picture, imgUnchecked.Picture)
.RowHeight(i) = ScaleY(imgChecked.Height, ScaleMode, vbTwips) + 15
Next
.Row = 0: .RowSel = 0
.Col = 0: .ColSel = 0
.Redraw = True
.Refresh
End With
End Sub
thanks for help!
Edited by - monchichi on 5/30/2007 12:59:52 AM
Count The Number Of Records In An Access Database
Hi
I am creating an Access database, and I need to put the information in some fields in my form into an array so I can perform some searches. I need to be able to count the number of records in my database (I think) so that I can declare the array my_array(numberofrecords, 8). Can anyone help me with the code to do this?
I have an auto number for each record as its primary key. Maybe this will make the coding easier, but I'm not sure.
I need the details to be updated each time a record is saved. Is this possible? I can't find any good sample code anywhere.
Thanks
Randomly Pick Records From An Access Database
hi,
my query is that i have a database of about 100 questions with question id 1-100. i need to develop a test program in which 25 questions would be selected randomly from the 100 questions and no question should be repeated ie every time the "next_question" button is clicked, a new random question should be displayed. could you kindly guide me how to go about this. sample code would be much appreciated. i'm using an accesss database.
thanks and regards.
Attaching Files To Access Database Records
Hi All,
I am working on an application in VB6, and I want my users to be able to attach pictures to the projects that they are creating with the application which will be sent to other users. Each project that a user creates is stored as a record in my Access database. I am wondering if it is possible to attach a file to each record. If this is possible, is it practical or should I just save the path of the file as a text field and copy each picture to a public server for other users to view? Also, how do I attach a file?
Thanks!
Delete Records In Access Database > 3 Days
I have an online database that records visiters per hour and day as well as most popular pages. After collecting info for a couple months it's getting too huge.
How can i delete records more than 3 days old thru vb.
This is what i have tried with no success:
Code:
Option Explicit
Dim db As Connection
Dim WithEvents adoPrimaryRS As Recordset
cmdDelete_Click()
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:Counters.mdb;"
'db.Execute " DELETE * FROM tblCounter WHERE Date < DATEADD(""d"", -3, NOW()) "
end sub
What am i doing wrong ?
Deleting Records From A Database (Microsoft Access)
Hi ive created a program to store movie names and their categories. Ive used a recordset to connect to the database from vb6 but i am having trouble deleting a record from the database from vb6.
If there is any other information you wish to know then please ask but if anyone knows an effective method to delete records from a databse or knows a website that would be of any help please post it.
Thanks
Pasting Records To Access Database From ODBC
Hi all,
I need help please !
How can I return a recordset from an external database (via ODBC link) and paste it into an Access database table.
I have worked out how to retrieve a recordset ok and have even been able to create a table in Access through code. However, I can't seem to fill my table in Access with the data from the recordset retrieved using ODBC. Is this the problem. One recordset is ODBC and the other MSJet - I just dunno.
Its all getting very technical for me - I aint even a programmer !!!
If anyone needs further explanation then please mail me at.
stewart_hafandeg@yahoo.co.uk
Someone please help, I have a deadline that i need to meet.
Cheers
Stew
Locating Records In Access Database Thru VB Code
I am using VB6.0. I have connected my MS Access 2000 database using ADODC. How can I search and display all records matching a criterion. For example, to display all records with surname beginning with 'MO' in the SurNamefield of the database.
Someone to help please.
Sam
Selecting Null Records From An Access Database
I have tables for clients, payments and property. I want to know who hasn't paid in 2008. I can't use =null because if he hasn't paid there isn't a record created. How can I determine who hasn't paid?
HELP!
Import/Export Of Access Database Records
I am searching for a way to import and export single record(s) from my vb database to another user of the database. I am using Access for the database. I am using VB6 sp6. I have been looking all over the net and can not find anything close to what I am asking for.
I brief:
Computer "A" has a record they want to give to Computer "B" that are not networked. They have two separate databases with the same file name. How do I export and import a single record to the systems.
Thanks in advance,
Ryan
Reading Specific Records From An Access Database
I am trying to retrieve specific records fomr an access database that I have, However the SQl query that I am using
some times thorws an error when it is executed
the error is : Syntax error (missing operator) in query expression
Here is the code that i am using:
SQL = "SELECT * FROM MP3s WHERE Album = '" & Avalue & "'"
rs.ActiveConnection = db
rs.CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
rs.LockType = ADODB.LockTypeEnum.adLockOptimistic
rs.Open(SQL)
From testing and debugging, I have found that I only get the error if the variable Avalue contains an ' . Is there any way to get around this issue.
Thanks in advance,
Vishal
Edited by - ZeroGravity on 5/26/2004 3:35:37 PM
Query Specific Records In VB/Access Database
I am working on a VB project that draws on an access database that is full of client addresses and other company information. My project is finished exept I can't figure out how to query specific records. It is a simple database but since there are about 2000 individual company records, scrolling through the entire database to find a record is obviously not the way I want to do it. I have a book that shows how to do this in SQL Server, but as I only have Access this is of no use. Any help would be greatly appreciated!!!
Need Help With Adding Records To Access Database By Using A Datagrid
I have an Access database installed on my Windows NT4 PC.
I am accessing the database table records from a VB6 program
that I wrote. I have the "Working Model Edition" of VB6
installed on my PC.
I am displaying te contents of a user specified table with a
combination of a datagrid control and an ADO data control.
The code which loads the database table contents into the
datagrid is located in a "module" which is included
with the VB project. The display of the data works fine.
I then modified the program to add a "modification" form and
added the "appropriate" new code to the "global items module".
The modification of table records worked fine.
I then created a form and new code to handle "record additions".
The "add a new record" row is displayed at the bottom of the data
grid (i.e. a blank row with a "*"). However, when I position my
cursor into the "add a new record" row I can't type anything. I
even tried setting the ado data control mode parameter to
"adModeReadWrite" at both design time and run time. This did not
solve the problem.
Here is the code from my "global items module" which handles the
display of the datagrid.
Public Sub AddTableContents(strTableName As String)
' Display the specified table in the form "frmTableAdd"
' inside the DataGrid named "dgrAddGrid" using
' an Adodc control. This grid allows record additions.
frmTableAdd.lblTableAdd.Caption = "Contents of table " & _
strTableName
frmTableAdd.adoTableAdd.CommandType = adCmdTable
frmTableAdd.adoTableAdd.RecordSource = strTableName
frmTableAdd.adoTableAdd.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;User ID=Admin;Data Source=F:Program FilesMicrosoft Visual StudioVB98
ewBiblio.MDB;Mode=Share Deny None"
frmTableAdd.adoTableAdd.Mode = adModeReadWrite
Set frmTableAdd.dgrAddGrid.DataSource = frmTableAdd.adoTableAdd
frmTableAdd.Show vbModal
Exit Sub
End Sub
Any ideas on what I have to change in order to be able to add new records ?
TIA for your help.
Display Records From Access Database On Form
hello..i need help displaying records stored in an access database on a form..would only like to display instead of deleting or updating..how do i do this? and how do i make a picture to fit the whole of a mdi form? thanks in advance.
Fetching Only Filled Up Records From Database(Access)
Dear friends,
I am working with Vb & Access2000 ,
I wish fetch records on Datareport or Cr-Reports those are filled up I mean if row contains "name adress pin phone email" but only name & email is filled up by user then i want to fetch only name & email on the report without living blank spaces for other records
if possible send querry example
thanking u in advance
Narendra
Archiving Records Held In An Access Database From VB
I am wanting to archive specific records within an Access 2000 database into a seperate database from Visual Basic. I know that I could do this using 'SELECT' and 'INSERT' sql statements, however this seems to me to be extremely long winded and I am sure there must be a quicker way of doing this.
Can anybody help me???
Ta
Tom
Using An SQL String To Call Data From Access Database Into VisualBasic DataGrid???..n
the Situtaion:.. I am using an access database that holds dates as strings. example( 06252004 ). I am trying to use an sql string to search everything that is selected from that database table, for dates specified as a start date and end date, which are specified by the user. The results should be placed on a datagrid.
All my sql attempts have failed.
Can anyone help me peice one together, in an SQL string format, that will work in VB and be accepted by access when vb passes it to it?
Doesnt Check All Records In Access Database Table
"tblRegister" holds more than one record but my program doesnt go through each record until a match is found, it only looks at the first record.
If anyone knows how I can implement this please let me know!
Thanks
Here is my code....
Code:
Private Sub Command1_Click()
Dim conConnection As New ADODB.Connection
Dim cmdCommand As New ADODB.Command
Dim rstRecordSet As New ADODB.Recordset
conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "" & "calculations.mdb;Mode=Read|Write"
conConnection.CursorLocation = adUseClient
conConnection.Open
With cmdCommand
.ActiveConnection = conConnection
.CommandText = "SELECT * FROM tblRegister;"
.CommandType = adCmdText
End With
With rstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdCommand
End With
'If rstRecordSet.EOF = True Then
rstRecordSet.MoveFirst
'rstRecordSet.MoveNext
If (Text1.Text = rstRecordSet!Username) And (Text2.Text = rstRecordSet!password) Then
MsgBox "Welcome"
Select Case True
Case OptEasy
frmEasy.Show
Label4.Caption = "Easy"
Case OptMedium
frmMedium.Show
Label4.Caption = "Medium"
Case OptHard
frmHard.Show
Label4.Caption = "Hard"
End Select
Hide
Else: MsgBox "Sorry, your details are invalid, please try again"
frmSettings.Show
frmEasy.Hide
frmMedium.Hide
frmHard.Hide
End If
rstRecordSet.Close
conConnection.Close
Set conConnection = Nothing
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
End Sub
Inserting, Saving Records From Flexgrid To Database(access..)
Dear Frnds,
I M Using Ms Access And Vb.6.0.
I Want To Insert Records In Flexgrid And Saving Records Into Table..
So How Can I Use Flexgrid..
Simply, I Have To Save , Insert Data Into Flexgrid..
I M Student And I M Developing One Software On Bank Test Key S/w.
So Plz Help Me..
Plz Give Me Some Link On Flexgrid.bcz I M New To Flexgrid....
Thanx In Advace
Sweethoney
Reading, Writing And Deleting Records In Access Database, Please Help!
Hia!
I'm a total amateur to VBS but need help on a VBS script. This is used in a HMI system from Siemens called WinCC, used for process viewing and control.
What I need to is to write to a table in Access, then read these values and then delete records (rows) in Access.
By using help functions in WinCC I have this set up:
I created an Access database with the WINCC_DATA table and columns (ID,
TagValue) with the ID as the Auto Value.
Then I can write to Access by the following code:
Dim objConnection
Dim strConnectionString
Dim lngValue
Dim strSQL
Dim objCommand
strConnectionString = "Provider=MSDASQL;DSN=SampleDSN;UID=;PWD=;"
lngValue = HMIRuntime.Tags("Tag1").Read
strSQL = "INSERT INTO WINCC_DATA (TagValue) VALUES (" & lngValue & ");"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open
Set objCommand = CreateObject("ADODB.Command")
With objCommand
.ActiveConnection = objConnection
.CommandText = strSQL
End With
objCommand.Execute
Set objCommand = Nothing
objConnection.Close
Set objConnection = Nothing
This actually works fine, pure luck I guess!
Can anyone help me with scripts for:
1. Reading one record (row) according to a ID number
2. Deleting the a record (row) according to a ID number
A complete script would of course be the best thing for a "stupid" amateurlike me! But I would be very thankful for any help!
Best regards
Vadar
Some Added Records Not Showing When Updating An Access Database
Ok, Here's the Skinny...
I am writing an application that pulls a list of names off of a
server, it runs a command prompt with a batch file, and outputs
to a text file. (There is an erlier post I put on here last week
regarding that portion of it.)
The problem is that when VB reads through the file and adds the
records, it appears to skip some of the records in the begginning
(appearing to be a random amount) I pasted the code below,
any ideas?
below is the code that creates the file
VB Code:
Private Sub Timer2_Timer() Dim ListCMD Dim BatchFile Dim ListLoc Dim lPid As Long Dim lHnd As Long Dim lRet As Long Timer2.Enabled = False BatchFile = App.Path & "atch.bat" ListLoc = App.Path & "list.txt" ListCMD = "c:" & vbNewLine & "cd " & vbNewLine & "cd " & App.Path & vbNewLine & "telalertc.exe -host jorma -list destinations > list.txt" Open BatchFile For Append As #1 Print #1, ListCMD Close #1 BatchFile = "cmd.exe /c " & Chr(34) & BatchFile & Chr(34) lPid = Shell(BatchFile, vbHide) If lPid <> 0 Then lHnd = OpenProcess(SYNCHRONIZE, 0, lPid) If lHnd <> 0 Then lRet = WaitForSingleObject(lHnd, dwMilliseconds) CloseHandle (lHnd) Timer3.Interval = 3000 Timer3.Enabled = True End If End If End Sub
Below is the code that reads the file
VB Code:
Sub ReloadFullList() Dim matrixdb As Database Dim SQLcmd Dim ListFile Dim BatchFile Dim log Set matrixdb = OpenDatabase(Form1.MatrixDBLocation) ListFile = App.Path & "list.txt" BatchFile = App.Path & "atch.bat" With Form1.Adodc2 .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Form1.MatrixDBLocation & ";Mode=ReadWrite;Persist Security Info=False" .RecordSource = "SELECT * from tblPagers" .Refresh End With SQLcmd = "DELETE * from tblpagers" WriteSQLLog (SQLcmd) matrixdb.Execute SQLcmd Open ListFile For Input As #1 Do Until EOF(1) Line Input #1, log If InStr(1, log, "error", vbTextCompare) Then GoTo ConnectError Exit Sub Else: End If Loop Close #1 Open ListFile For Input As #1 Line Input #1, log Line Input #1, log Do Until EOF(1) Line Input #1, log log = Mid(log, 2, Len(log) - 2) With Form1.Adodc2.Recordset .AddNew !pagers = log .Update End With Loop Form1.Adodc2.Recordset.MoveFirst Close #1 Form1.List1.Clear Do Until Form1.Adodc2.Recordset.EOF = True Form1.List1.AddItem Form1.DataGrid2.Text Form1.Adodc2.Recordset.MoveNext Loop Form1.Adodc2.Recordset.MoveFirst Kill BatchFile Kill ListFile MsgBox "Jorma Full List Update Complete, Dont Forget to Push to Server When done.", vbOKOnly + vbInformation, "Jorma List Update Complete" Unload Form4Exit SubConnectError: Close #1 MsgBox "There was an error connecting to Jorma to retreieve the full list, please try again later." & vbNewLine & vbNewLine & "If the problem persists, please contact your System Administrator", vbOKOnly + vbInformation, "Potential Jorma Issue" Unload Form4 Kill BatchFile Kill ListFileEnd Sub
The part where it skips two lines is the header for "connecting to,
pulling list" blah blah, I dont need that in the database, all I need
is the names.
The text file is complete, but the names in the database seem to
start at a different part of the list, sometimes 3 names down,
sometimes 20 names down... it makes absolutely no sense to
me, I even set up a timer to have it wait 3 seconds before it
reads the list, but that doesnt seem to make a difference either!!
Any suggestions would be greatly apperciated
Fastest Way To Add 500 000 Records To A List View From A Access Database??
Just testing a few things here but what is the fastest way to add records from a database to a list view box?
Very simple table has 2 fields ID, name and only "name" is used!
This is what i have:
VB Code:
Public Sub serverfillfields() Serverrs.Open "SELECT filename FROM tbl_server", cn, adOpenKeyset, adLockPessimistic, adCmdText lsv1.ColumnHeaders.Add = "Filename" Dim intRecCount As Long Dim intCounter As LongServerrs.MoveLastintRecCount = Serverrs.RecordCountpb1.Max = intRecCountServerrs.MoveFirstDoEventsFor intCounter = 1 To intRecCount lsv1.ListItems.Add , , ("" & Serverrs.Fields("filename")) pb1.Value = intCounter Serverrs.MoveNextDoEventsNext intCounterDoEventsEnd Sub
Anyone got something faster??
later
b
p.s yes i know b4 everyone says i should think about using another db etc etc. and i probably will.
Inserting Records Into Access Database Using OleDb.OleDbCommand
I have looked through this site carefully and have not found an answer written simply enough for me to incorporate into my "program".
I'm very new to VB .Net. I am using 2005 Express. I have done no programming of any importance. I need to be able to insert records (rows) into a MS Access database via the VB interface I have designed.
What I a trying to do is:
1. Connect to an empty MS Access database that has the following attributes.:
a. 1 table "Points"
b. 3 columns "ID", "Point_X", "Point_Y"
c. A primary key "ID" - It is not set to autonumber
d. All fields are number
2. Send the Command "INSERT INTO Points (ID,PointX, PointY)" & "Values(0,1,2)"
3. Close my program.
4. Open my program.
5. Retrieve the record from the database.
6. Display the record in a listbox
I will need very precise instructions as I am missing the entire picture.
The record does save to the database. I have tried every technique I found on this site, but I am unable to implement
any of the ideas successfully.
I would greatly appreciate any help. Thanks in advance.
Here is what I have so far:
Code:
Imports System.Data
Imports System.Data.OleDb
Public Class AddRemove
Dim provider As String = "Provider = Microsoft.Jet.OLEDB.4.0;"
Dim source As String = "Data Source=PlottedPoints.mdb;"
Dim userInfo As String = "User ID=admin; Password=''"
Dim connectStr As String = provider & source & userInfo
Dim db_Conn As New OleDb.OleDbConnection(connectStr)
Dim sqlStr As String = "SELECT * FROM Points"
Private Sub AddRemove_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Dim db_CommandBuilder As New OleDb.OleDbCommandBuilder(db_Adapter)
'db_Adapter.Fill(ds, "Points")
''For Each row As DataRow In dt.Rows
' lstPoints.Items.Add("Point {" & row(1).ToString & " ," & row(2).ToString & "}")
'Next
End Sub
Private Sub AddPoint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddPoint.Click
Try
'Define connection string
Dim db_CommandStr As String = "INSERT INTO Points (Point_X, Point_Y)" & "Values(1,2)"
Dim db_Adapter As New OleDb.OleDbDataAdapter(sqlStr, db_Conn)
db_Conn.Open()
Dim db_Command As New OleDb.OleDbCommand(db_CommandStr, db_Conn)
db_Command.ExecuteNonQuery()
db_Conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
lstPoints.Items.Clear()
ListPoints()
End Sub
Private Sub ListPoints()
Dim table As New DataTable
Dim db_Adapter As New OleDb.OleDbDataAdapter(sqlStr, db_Conn)
db_Adapter.Fill(table)
table.TableName = "Points"
db_Adapter.Dispose()
For Each col As DataRow In table.Rows
lstPoints.Items.Add("{" & col(1) & ", " & col(2) & ")")
Next
End Sub
End Class
Edited by - mkruncher@hotmail.com on 1/28/2007 4:06:34 PM
|
TRACKER
