Load data to database

[JohnPJ]

Hi All,

I am very new to VB script and I have done some VBA macros in Excel before.
I have been recently assigned to write / modify existing script which is written in VB to load data from source to the database?

Probably I have to write entirely new code , but not sure. So to start with what I should do? Is there any tools that I should Install in my computer. Some headsup will be very helpful.

Thanks,

John

[MarkT]

A good starting place may be the ADO tutorial in the FAQ section. http://www.vbforums.com/showthread.php?t=551154

[JohnPJ]

Hi,

I recieved compilation error "Variable not defined" in Public Sub FillFields() function.

I created a userform in Excel and added all your codes.
Please let me know how can I fix the compilation error.

Thanks,

John

[JohnPJ]

Thanks Mark.
Unfortunately it throws compilation error. I have posted that to the original post.
Thanks

[si_the_geek]

The FAQ forum is not the place to post your questions, so I have moved your post into your own thread.

I recieved compilation error "Variable not defined" in Public Sub FillFields() function.

Based on the amount of people who have used that tutorial and not mentioned the same issue, I suspect you have made a mistake somewhere.

If you tell us which line of the code gives an error, we can help work out what the cause is, and tell you how to fix it.

I created a userform in Excel and added all your codes.

In that case you aren't using VBScript, but VBA... thread moved to 'Office Development/VBA' forum.

[JohnPJ]

Thank You.
The error I recieved is in the below piece of code,
The error is " Compile Error : Variable not defined" and highlighted on Public Sub FillFields()

Public Sub FillFields()
    If Not (rs.BOF = True Or rs.EOF = True) Then    'Checks if we are at the first or last record. This is use a lot.
        Text1.Text = rs.Fields("Field2")            'text1 = field2 and display that data
        Text2.Text = rs.Fields("Field3")            'as above
        Combo1.Text = rs.Fields("Field1")           'as above
    Else
        'reset the textbox and combobox if there are no more records
        Text1.Text = ""
        Text2.Text = ""
        Combo1.Text = ""
    End If
End Sub

[si_the_geek]

It shouldn't be highlighting that line... try starting your program with Ctrl-F5 instead, as that should highlight the actual error line.

I suspect it is the If line that causes the problem, in which case check where you declared the variable rs. It should be in the Declarations section at the very top of the code file.

[JohnPJ]

Thanks for checking. I appreciate it.
Now it gives compile error in this code, variable not defined Me.MousePointer = vbHourglass

I am using Excel2007 and copied all the codes as the author provided. For your question, Yes, I have declared all the variables in Declarations section.

Below is my declaration section

Option Explicit
Public cn As ADODB.Connection  'this is the connection object
Public rs As ADODB.Recordset   'this is the recordset object

Am I missing some thing? Thanks..
------------------------------------------------
Private Sub Form_Load()
'turn MousePointer to HourGlass to show that we are busy processing
Me.MousePointer = vbHourglass

'instantiate the connection object
Set cn = New ADODB.Connection
'specify the connectionstring
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\DB1.mdb"
'open the connection
cn.Open

'instantiate the recordset object
Set rs = New ADODB.Recordset
'open the recordset
With rs
.Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable

'loop through the records until reaching the end or last record
Do While Not .EOF
Combo1.AddItem rs.Fields("field1")
rs.MoveNext 'moves next record
Loop

If Not (.EOF And .BOF) Then
rs.MoveFirst 'go to the first record if there are existing records
FillFields 'to reflect the current record in the controls
End If

End With

Me.MousePointer = vbNormal 'sets the mouse pointer to the normal arrow
End Sub

[si_the_geek]

That particular code is specific to VB6, as is the location of the code.

For Excel you need to put the code in UserForm_Initialize (rather than Form_Load), and use these lines for the mouse:

Me.MousePointer = fmMousePointerHourGlass

Me.MousePointer = fmMousePointerDefault

[JohnPJ]

Yes I did the same. But upon compiling the error is Compile Error : Invalid use of me Keyword. Is the isssue with the references? Can you please give me some suggestions.
Thanks,

John

[si_the_geek]

It is very unlikely to be a Reference issue... it is almost certainly something specific to Excel forms, but I don't have enough experience with them to know the correct way to write those two lines (they aren't essential tho, so could be commented out in the short term).

[JohnPJ]

No Problem and thanks for your help.
I commented those 2 lines. There is a compilation error saying that variable not defined on "App.Path". Can you please let me know how to overcome this issue.
Thanks,

[si_the_geek]

App.Path is something else that is VB6 specific, and I don't know the Excel VBA equivalent.

What you can do is put the full path instead, eg:
"Data Source=c:\folder\DB1.mdb"