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.

 

[bjd4jc]

You could use an sql statement similar to the following:

ALTER Table MyTable ADD COLUMN NewColumnName Text(50)

 

[Askeladden]

Would this SQL statment work inside a loop?

The code I have looks like this:

  Dim LinkFelt As Integer, i As Integer
  
  LinkFelt = 1
  
  For i = LinkFelt - 1 To lstDocs.ListCount - 1
    datAccess.Recordset.Fields("Link", LinkFelt) = lstDocs.List(LinkFelt - 1)
    LinkFelt = LinkFelt + 1
  Next i

Here I am attempting to make a field name in my Access database. The name of the field will be Link.
But, how many fields I need created, is determined by how many entries that a listbox has. Each Field created should be Link, followed by a number. (For exp. Link1, Link2, Link3, etc.

My code does not precisely work as I had in mind.
Could anyone help me out?
Thank ahead of time.

 

[fredericofonseca]

All SQL statements can work on a loop.

dim conn as adodb.connection
dim i as long
dim sSql as string
for i = 1 to 10
sSql = "ALTER Table MyTable ADD COLUMN " & link & i & " Text(50)"
conn.execute sSql
next i

Something similar to the above.

If you are unsure that a field is already on the table you will need first to load the field names from the table, and then loop through them before adding the new ones. There is code for this on these forums so search for it if you need.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[johnwm]

The word link is a string literal. You may need to change:

sSql = "ALTER Table MyTable ADD COLUMN " & link & i & " Text(50)"

to:

sSql = "ALTER Table MyTable ADD COLUMN link" & i & " Text(50)"

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Askeladden]

I get a run-time error as I run my program.

Here is my code to save a listbox:

Private Sub LinkSave()
  'Loop to save Listbox Arrays into named fields in, Access, record
  
  Dim LinkFelt As Integer, i As Integer
  Dim strSQL As Integer
  
  LinkFelt = 1
  
  For i = LinkFelt - 1 To lstDocs.ListCount - 1
  
    'set SQL string
    strSQL = "ALTER Table [Vedlikeholds Rutiner] ADD COLUMN Link" & LinkFelt & " Text(50)"
    
    '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
End Sub

Here I am attempting to create new fields in a Microsoft access file, one field for each of the indexes in a listbox, and save these idexes. The fields should be named link1, link2, etc.
But I Keep on getting Runtime error 13, And when I go to debug it highlights the strSQL = "ALTER Table [Vedlikeholds Rutiner] ADD COLUMN Link" & LinkFelt & " Text(50)".
Can somebody please help me solve this proplem?

Christian

 

[Askeladden]

opps, my mistake. I had strSQL as an intiger, I have corrected this to a string. But now the program will not take the .refresh, in the With clause.

So now the code is:

Private Sub LinkSave()
  'Loop to save Listbox Arrays into named fields in, Access, record
  
  Dim LinkFelt As Integer, i As Integer
  Dim strSQL As String
  
  LinkFelt = 1
  
  For i = LinkFelt - 1 To lstDocs.ListCount - 1
  
    'set SQL string
    strSQL = "ALTER Table [Vedlikeholds Rutiner] ADD COLUMN Link" & LinkFelt & " Text(50)"
    
    '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
End Sub

 

[fredericofonseca]

Did you try with the ADO example I gave you? a data access control may not work well.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Askeladden]

Yes, Frederico, I tried it. I incorpoated your suggestion into my save module, but I forgot to include

Dim Conn As ADODB.Connection

and

Conn.Execute strSQL

I have included these now, but I get a
run time error '91'
Object variable or with block variable not set, as I run the program + when I debug it, the statement

Conn.Execute strSQL

is highlighted.
I also tried to run your example in it's pure form

Dim conn As adodb.Connection
Dim i As Long
Dim sSql As String
For i = 1 To 10
  sSql = "ALTER Table MyTable ADD COLUMN link" & i & " Text(50)"
  conn.Execute sSql
Next i

and the same thing happened.
Any suggestion on why, and how I might correct this problem?
Thanks.

 

[fredericofonseca]

you need to add
set conn = new adodb.connection
after
dim ssql as string

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Centerfuge]

You need the connection established, connected and opened...

Dim oc As ADODB.Connection

Set oc = new ADODB.Connection
oc.connectionstring = "someconnectionstring"
oc.open

strSQL = "someSQL"
oc.execute(strSQL)

 

[Askeladden]

I just got back from a vacation (got myself married), to the program, and I inputed the changes you suggested Frederico. An error message told me I needed to open the file. So I followed Centerfuge's suggestion, and added

conn.connectionstring = "someconectionstring"
conn.open

here I assumed that "someconectionstring" meant the pathnameof the database file, so it became

Conn.ConnectionString = "c:\IKDV\Vedlikeholds_Rutiner.mdb"
conn.open

But now I get an error "that there is no data sourcefile or standard driver given".

I would guess I need to modify the "ConnectionString" to include the driver, but if i'm right, what might this be.

Thanks
Christian

 

[HarleyQuinn]

Hi Christian,

If you use:

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

That should work.

Hope this helps

Harleyquinn

 

[Askeladden]

Thanks Harleyquinn.
That got me past that problem, however now an error message sais it can not lock down the table in the file, its being used by the maskin.

Christian

 

[HarleyQuinn]

Christian,

Is the table open or in use by another part of the program when you are trying to alter it??

If so it will need to be closed when you perform this part of the code.

Hope this helps

Harleyquinn

 

[Askeladden]

Yes the table is being used by another part of the program.
How would I then close it.

Christian

 

[HarleyQuinn]

Hi,

How is it being used in the other part of the program??

Harleyquinn

 

[Askeladden]

It is being used to read in information to textboxes in the main form of this program

 

[Askeladden]

Plus, also to input chances to the access file from the program

 

[HarleyQuinn]

Hi,

If you close the connection to the DB each time you have populated the textboxes that should clear the problem for you.

Hope this helps

Harleyquinn