Using Visual Basic 6.0 To Create New Fields in Microsoft Access 3

[Askeladden]

I need to create New Fields in Access using VB6.0. Is ther a way I might do this? If so, how?
Thanks ahead of time.

 

[HarleyQuinn]

And each time you have updated the database also...

Harleyquinn

 

[Askeladden]

Would it work if I closed it at the beginning of my save procedure?
How, then would I code it?
Also how would I reopen it, so that it can continue to populate the texboxes of the program.
Thanks.
Christian

 

[HarleyQuinn]

Hi,

Just my personal way of doing it but I tend to close the connection when I have finished the operation I was using it for.

If you are using the ADO solution then

conn.close
Set conn = Nothing

Will close it and you would continue to open it the way you have been to populate the textboxes.

Hope this helps

Harleyquinn

 

[Askeladden]

Thanks, I'll try it.

Christian

 

[Askeladden]

Would I have to redo the

Dim Conn As ADODB.Connection

above your closing code, since I then set it to the conn to "noting

 

[HarleyQuinn]

Hi,

No, I have just tested this (for my own peace of mind more than anything else) and even after setting it to 'Nothing' you can still use the original connection you declared.

Hope this helps

Harleyquinn

 

[Askeladden]

Where should I place the "conn.close" and the "set conn = noting" in my save procedure?
Do you have a suggestion?

Christian

 

[HarleyQuinn]

Hi,

I would put it straight after you have finished populating the Textboxes or updating the Access table. That way it will be closed everytime you want to use it again.

Cheers

Harleyquinn

 

[Askeladden]

I inputed the code before the
"Set Conn = New ADODB.Connection" statment, and got an error Message 91 Object variable or with block variable not set:

My code, now, is:

Dim Conn As ADODB.Connection
  Dim LinkFelt As Long, i As Long
  Dim strSQL As String
  
  Conn.Close
  Set Conn = Nothing

  Set Conn = New ADODB.Connection
  LinkFelt = 1
  
  Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Password=;User ID=;" & _
        "Data Source=c:\IKDV\Vedlikeholds_Rutiner.mdb;" & _
        "Persist Security Info=True;"
  
  Conn.Open
  
  For i = LinkFelt - 1 To lstDocs.ListCount - 1
  
    'set and execute SQL string
    strSQL = "ALTER Table [Vedlikeholds Rutiner] ADD COLUMN Link" & LinkFelt & " Text(50)"
    Conn.Execute strSQL
    
    'To set and refresh datAccess
    With datAccess
      .RecordSource = strSQL
      .Refresh
    End With
    
    'To Save an entry of the listbox into the newly created field
    datAccess.Recordset.Fields("Link" & LinkFelt) = lstDocs.List(LinkFelt - 1)
    
    LinkFelt = LinkFelt + 1
  Next i

Thanks.
Christian

 

[HarleyQuinn]

Hi,

Just had a brain wave, It might be easier for me to help if you post the code you use to populate the textboxes as well, so you can close the connection when they are populated.

Out of interest (this will not solve your current problem but might help solve future ones, if you place the:

Conn.Close
Set Conn = Nothing

After the 'next i' at the very bottom of your code example it will close the connection you are using to alter the table so you can the connection later.

Cheers

Harleyquinn

 

[Askeladden]

Thanks, Harleyquinn.
I set, within the property box, the datasource to point to the data tool, from the toolbox, where the connection to the access file is made.
Then I set, also within the propertybox, the datafield.

Christian

 

[Askeladden]

Ex. within a textbox

datasource datAccess
and
datafield txtFritekst2

datAccess points to my access file

 

[HarleyQuinn]

Hi,

I'm going to be honest, I've never used a data control in VB.

I am assuming that it will keep an open connection to the database's table until told not to??

Therefore it might be difficult to use a work-around without getting rid of the control...

Cheers

Harleyquinn

 

[AEtherson]

your problem is here

Conn.Close
Set Conn = Nothing

if u want to do this you have to check the connection state

If Conn.state = adstateopen then

Conn.Close
Set Conn = Nothing

end if

 

[Askeladden]

Thanks AEtherson.
But now I get the "error message 91 Object variable or with block variable not set" again.
Any idea what might be cousing it?

 

[Askeladden]

Harleyquinn.
You are right, I believe. I have used data controls in vb,
but never in code.
I have heard about it, thought, and I know, what I need done here, in my program, will work.
However I might need to do so rewriting. I thank you and everybody for the help you and they have provided.

Christian

 

[HarleyQuinn]

Hi,

Here is the bones of a project I have just knocked up from memory. It should do something like what you are after:

Place this in a module called Module1:

Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset

Sub Connectivity(strSQL As String)
Dim connstring As String

connstring = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Password=;User ID=;" & _
        "Data Source=c:\IKDV\Vedlikeholds_Rutiner.mdb;" & _
        "Persist Security Info=True;"
cn.Open connstring

cn.CursorLocation = adUseClient

Debug.Print strSQL

rs.Open strSQL, cn
rs.MoveNext
If rs.EOF = True Or rs.BOF = True Then
    MsgBox "Not found"
Else
    Form2.Text1.Text = rs!YOUR_TBL_FIELD
'you can add extra fields in...
End If

End Sub

Then add this on Form_Load

Connectivity ("SELECT YOUR_TBL_FIELD FROM TBL_YOUR_TABLE")

Then add two command buttons and code as follows:

'this button as a record selector (only one way)
Private Sub Command5_Click()
Module1.rs.MoveNext
If Module1.rs.BOF = True Or Module1.rs.EOF = True Then
MsgBox "That's all of the records"
Module1.rs.Close
Module1.cn.Close
Set Module1.rs = Nothing
Set Module1.cn = Nothing
Else
Text1.Text = Module1.rs!YOUR_TBL_FIELD
Form2.Refresh
End If
End Sub


'this button alters the table
Private Sub Command6_Click()
Module1.rs.Close
Module1.cn.Close

Dim i As Long
Dim strSQL2 As String



  Module1.cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Password=;User ID=;" & _
        "Data Source=c:\IKDV\Vedlikeholds_Rutiner.mdb;" & _
        "Persist Security Info=True;"

  Module1.cn.Open

  For i = LinkFelt - 1 To lstDocs.ListCount - 1


    strSQL2 = "ALTER Table [TBL_YOUR_TABLE] ADD COLUMN Link" & linkfelt & " Text(50)"
    Module1.cn.Execute strSQL2
  Linkfelt = linkfelt + 1
  Next i

Module1.cn.Close

Connectivity ("SELECT YOUR_TBL_FIELD FROM TBL_YOUR_TABLE")
End Sub

You can easily add another button(s) to act like a back button for previous records and other functions provided by the Data control.

Please note that the code above is only very briefly tested and does work using my DB's etc, but may not work for your exact spec.

I only coded it with the knowledge of which order I had to close the connections. Please use AEtherson's code to check if the connection etc. is open before you try to create a new one.

You might not take this over the Data control but I thought I would show you another way of doing it.

Hope this helps

Harleyquinn

 

[HarleyQuinn]

Sorry about that, was writing the post before I saw your final reply.

Cheers

Harleyquinn

 

[dilettante]

Isn't it just easier to open and use a single database connection through the life of the program?

 

[AEtherson]

Dim Conn As New ADODB.Connection
Dim StrCon As String

StrCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=;User ID=;" & _
"Data Source=c:\IKDV\Vedlikeholds_Rutiner.mdb;" & _
"Persist Security Info=True;"

Conn.Open StrCon


For i = LinkFelt - 1 To lstDocs.ListCount - 1
'set and execute SQL string
Conn.Execute "ALTER Table [Vedlikeholds Rutiner] ADD COLUMN Link" & LinkFelt & " Text(50)"
Next


If Conn.State = adStateOpen Then Conn.Close
Set Conn = Nothing


Then Refresh your control(s)