EXCEL as a Database

[t3hfr3ak]

I know theres a menu button for it somewhere but i cant seem to remember how to do it.

This is for my movie information Database (I dont know if any of you remember when I was asking about storing the data in a txt file. Well now I am trying to get it in a EXCEL document. Any help would be appreciated. :D

[DenisOxon]

Hi,

For those of us who don't remember what are you trying to do ?

Denis

[t3hfr3ak]

Hi,

For those of us who don't remember what are you trying to do ?

Denis

I am making a movie information program Which has 3 forms

Form 1: The Main form where it tells you basic info about the program and 3 command buttons.

Form 2: Used to enter the information about the movie into text boxes to save into a file for later use

Form 3: Used to view the information entered previously

[QVeen72]

Hi,

Check This Code:

Private Sub cmdOpenExcel_Click()
  '
  Dim ACn As New ADODB.Connection
  Dim RST As New ADODB.Recordset
  '
  With ACn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=C:\MyExcel.xls ;Extended Properties=Excel 8.0;"
    .CursorLocation = adUseClient
    .Open
  End With
  '
  SET RST =Nothing
  RST.Open "select * from  [XL_SHEETNAME]", ACn, adOpenDynamic, adLockReadOnly
  Do While Not RST.EOF
     Debug.Print RST(0)
     RST.MoveNext
  Loop
  RST.Close
  ACn.Close
  '
End Sub

Regrads
Veena

[t3hfr3ak]

hmm... that honestly doesnt make too much sense too me... care to elaborate?

[QVeen72]

Hi,

i thought u wanted to connect to Excel thru "ADO" onnection..

Regards
Veena

[t3hfr3ak]

no i want to write and read variables to a spreadsheet

[manoshailu]

hi,

'declare the objects for connection and recordset
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

'Create object for connection
Set con = CreateObject("ADODB.Connection")
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\ioc\excel\ioc.xls;" & _
"Extended Properties=Excel 8.0;"

'apply connection string
con.ConnectionString = strcon

'Open the Connection
con.Open

'Create object for recordset
Set rst = New ADODB.Recordset

'Exp i use table employee
rst.Open "select * from employee", con, adOpenDynamic, adLockOptimistic

'To add a record
rst.AddNew
rst(0) = Trim(Combo1.Text)
rst(0) = Trim(Text2.Text)
rst(2) = Trim(Text1.Text)
rst.update

Same thing for search,update and delete records. Try this coding

al the best
shailu

[DenisOxon]

I am making a movie information program Which has 3 forms

Form 1: The Main form where it tells you basic info about the program and 3 command buttons.

Form 2: Used to enter the information about the movie into text boxes to save into a file for later use

Form 3: Used to view the information entered previously

Hi,

A question about each form

1) what are three buttons for ?

2) what file are you going to save them into ?

3) Is this displaying a spreadsheet ?

Denis

[t3hfr3ak]

I know theres a none code way to do it if you follow buttons in VB. I was doing it before there was a "Create database using excel or foxpro or access or a text file ect.

1) The three buttons are on the MAIN form.
Button 1. Displays the input form to enter movie info and store the variables to a text file (I would like to store them to an excel file)
Button 2. Displays the form where you pick a movie and it will display on the information about the movie
Button 3. Closes the project

3) No it does not display a spreadsheet, I just want it to take the variables FROM the spreadsheet and throw them in a few labels.

[manoshailu]

hi,

its good to satisfy ur requirements.

For Button 1 write the below coding
form2.show

In that form2 u write the below coding to store
'declare the objects for connection and recordset

Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

'Create object for connection

Set con = CreateObject("ADODB.Connection")
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\ioc\excel\ioc.xls;" & _
"Extended Properties=Excel 8.0;"

'apply connection string
con.ConnectionString = strcon

'Open the Connection
con.Open

'Create object for recordset
Set rst = New ADODB.Recordset

'Exp i use table employee
rst.Open "select * from employee", con, adOpenDynamic, adLockOptimistic

'To add a record
rst.AddNew
rst(0) = Trim(Combo1.Text)
rst(1) = Trim(Text2.Text)
rst(2) = Trim(Text1.Text)
rst.update

For Button 2 write the below coding
form3.show
same coding given for search but in the query u have to use where condition by my example
select * from employee where empid = '" & combo1.text & "'
and then write coding as
text1.text=rs(1)
text2.text=rs(2)

And finally for Button 3 write the below coding
exit

regards
shailu.