Compile Error: User Defined Type

[forrestgump]

I am currently trying to use the below VBA to import information into excel from access. This VBA is in the excle sheet:-

 Public Sub getrs() 
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String
sql = "Select * from Table1"
filenm = "R:\HR\HR_System_Reports_Folder\Databases\HeadCount.mdb"
Call GetCn(adoconn, adors, sql, filenm, "", "")
Dim xlsht As Excel.Worksheet
Set xlsht = Sheets("Sheet1")
xlsht.Range("A1").CopyFromRecordset adors
adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlsht = Nothing
End Sub

However when I try to use this code it says Compile Error: User Defined Type Not Defined. Ibelieve I need to tick somethin in the references section but I do not know what. Has anyone got any ideas?

[Dököll]

I am currently trying to use the below VBA to import information into excel from access. This VBA is in the excle sheet:-

[CODE=VB]

Public Sub getrs()
Dim adoconn As ADODB.Connectio n
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String
sql = "Select * from Table1"
filenm = "R:\HR\HR_Syste m_Reports_Folde r\Databases\Hea dCount.mdb"
Call GetCn(adoconn, adors, sql, filenm, "", "")
Dim xlsht As Excel.Worksheet
Set xlsht = Sheets("Sheet1" )
xlsht.Range("A1 ").CopyFromReco rdset adors
adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlsht = Nothing
End Sub

[/CODE]
However when I try to use this code it says Compile Error: User Defined Type Not Defined. Ibelieve I need to tick somethin in the references section but I do not know what. Has anyone got any ideas?

Are you doing this from Access, or Excel forrestgump?

[forrestgump]

Are you doing this from Access, or Excel forrestgump?

I am currently running this VBA in excel to try and pull the information from access.

[ADezii]

I am currently trying to use the below VBA to import information into excel from access. This VBA is in the excle sheet:-

Public Sub getrs()
Dim adoconn As ADODB.Connectio n
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String
sql = "Select * from Table1"
filenm = "R:\HR\HR_Syste m_Reports_Folde r\Databases\Hea dCount.mdb"
Call GetCn(adoconn, adors, sql, filenm, "", "")
Dim xlsht As Excel.Worksheet
Set xlsht = Sheets("Sheet1" )
xlsht.Range("A1 ").CopyFromReco rdset adors
adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlsht = Nothing
End Sub

However when I try to use this code it says Compile Error: User Defined Type Not Defined. Ibelieve I need to tick somethin in the references section but I do not know what. Has anyone got any ideas?

1. You may be missing a Reference to the Microsoft Access XX.X Object Library, this could account for the Error.
2. You should Instantiate the Object Variables pointing to the ADO Connection and Recordset Objects as in:
   [CODE=vb]
   'Declarations
   Dim adoconn As ADODB.Connectio n
   Dim adors As ADODB.Recordset
   
   'Creation of New Object Instances
   Set adoconn = New ADODB.Connectio n
   Set adors = New ADODB.Recordset[/CODE]
3. The problem may be with the GetCn Routine, namely:
   [CODE=vb]GetCn(adoconn, adors, sql, filenm, "", "")[/CODE]
4. Can you post the code for this Routine. This would enable the entire process to be duplicated, and hopefully, enable a solution to your problem.

[missinglinq]

I think Adezii's first comment is at least an important part of the problem here! Anytime you get this error message and there isn't a user defined function involved, it means that there's a function Access assumes to be user-defined because it can't find it in any of the referenced libraries. This is either because the library isn't referenced or the fumction name is misspelled.

Welcome to TheScripts!

Linq ;0)>

P.S. I understand your frustration, Forrest! Everyone keeps yelling "Run, Forrest, run!" and the blasted code just won't run!

[forrestgump]

I think Adezii's first comment is at least an important part of the problem here! Anytime you get this error message and there isn't a user defined function involved, it means that there's a function Access assumes to be user-defined because it can't find it in any of the referenced libraries. This is either because the library isn't referenced or the fumction name is misspelled.

Welcome to TheScripts!

Linq ;0)>

P.S. I understand your frustration, Forrest! Everyone keeps yelling "Run, Forrest, run!" and the blasted code just won't run!

I have got it to run! here is the full code. I also needed to swuitch something on in the reference table.

Public Sub GetCn(ByRef dbcon As ADODB.Connectio n, ByRef dbrs As ADODB.Recordset , _
sqlstr As String, dbfile As String, usernm As String, pword As String)
Set dbcon = New ADODB.Connectio n
dbcon.Open "PROVIDER=Micro soft.Jet.OLEDB. 4.0;Data Source=" & dbfile & ";", _
usernm, pword
Set dbrs = New ADODB.Recordset
dbrs.Open sqlstr, dbcon
End Sub

Public Sub getrs()
Dim adoconn As ADODB.Connectio n
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String
sql = "Select * from table1"
filenm = "R:\HR\HR_Syste m_Reports_Folde r\Databases\Hea dCount.mdb"
Call GetCn(adoconn, adors, sql, filenm, "", "")
Dim xlsht As Excel.Worksheet
Set xlsht = Sheets("Sheet1" )
xlsht.Range("A1 ").CopyFromReco rdset adors
adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlsht = Nothing
End Sub

I need to find a way of pulling multiple queries, stacked, with a blank line in between. Any ideas?

[ADezii]

I have got it to run! here is the full code. I also needed to swuitch something on in the reference table.

Public Sub GetCn(ByRef dbcon As ADODB.Connectio n, ByRef dbrs As ADODB.Recordset , _
sqlstr As String, dbfile As String, usernm As String, pword As String)
Set dbcon = New ADODB.Connectio n
dbcon.Open "PROVIDER=Micro soft.Jet.OLEDB. 4.0;Data Source=" & dbfile & ";", _
usernm, pword
Set dbrs = New ADODB.Recordset
dbrs.Open sqlstr, dbcon
End Sub

Public Sub getrs()
Dim adoconn As ADODB.Connectio n
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String
sql = "Select * from table1"
filenm = "R:\HR\HR_Syste m_Reports_Folde r\Databases\Hea dCount.mdb"
Call GetCn(adoconn, adors, sql, filenm, "", "")
Dim xlsht As Excel.Worksheet
Set xlsht = Sheets("Sheet1" )
xlsht.Range("A1 ").CopyFromReco rdset adors
adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlsht = Nothing
End Sub

I need to find a way of pulling multiple queries, stacked, with a blank line in between. Any ideas?

Please describe in greater detail.

[forrestgump]

Please describe in greater detail.

The code above pulls in one query, well actually one table 'table1', but lets say I also want to pull through 'table2' and 'table3' onto the worksheet and separate the results by a blank row??

I.e. I want to pull through 3 tables onto 1 worksheet and separate the results by a space.

Regards,

Forrest

[ADezii]

The code above pulls in one query, well actually one table 'table1', but lets say I also want to pull through 'table2' and 'table3' onto the worksheet and separate the results by a blank row??

I.e. I want to pull through 3 tables onto 1 worksheet and separate the results by a space.

Regards,

Forrest

I imagine that it could be as simple as a Row insertion between each CopyFromRecords et.

[james 125877]

hi

i got a problem when i tried to compile it, i had an error and it:

The type or namespace name 'fclsViewer' could not be found (are you missing a using directive or an assembly reference?)

now the code is: using System;
using System.Collecti ons.Generic;
using System.Componen tModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows. Forms;


namespace Picture_Viewer
{
public partial class Form1 : Form
{
public Form1()
{
InitializeCompo nent();
}

private void button1_Click(o bject sender, EventArgs e)
{
Application.Run (new flcsViewer());
}

private void Form1_Load(obje ct sender, EventArgs e)
{
// Show the open file dialog box.
if (ofdSelectPictu re.ShowDialog() == DialogResult.OK )
{
//Load the picture into the picture box.
picShowPicture. Image = Image.FromFile( ofdSelectPictur e.FileName);
// Show the name of the file in the form's caption.
this.Text = String.Concat(" Picture Viewer (" + ofdSelectPictur e.FileName + ") ");
}

}
}
}


Please help me i really want it to work !!!

[ADezii]

hi

i got a problem when i tried to compile it, i had an error and it:

The type or namespace name 'fclsViewer' could not be found (are you missing a using directive or an assembly reference?)

now the code is: using System;
using System.Collecti ons.Generic;
using System.Componen tModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows. Forms;


namespace Picture_Viewer
{
public partial class Form1 : Form
{
public Form1()
{
InitializeCompo nent();
}

private void button1_Click(o bject sender, EventArgs e)
{
Application.Run (new flcsViewer());
}

private void Form1_Load(obje ct sender, EventArgs e)
{
// Show the open file dialog box.
if (ofdSelectPictu re.ShowDialog() == DialogResult.OK )
{
//Load the picture into the picture box.
picShowPicture. Image = Image.FromFile( ofdSelectPictur e.FileName);
// Show the name of the file in the form's caption.
this.Text = String.Concat(" Picture Viewer (" + ofdSelectPictur e.FileName + ") ");
}

}
}
}


Please help me i really want it to work !!!

You had it running until you switched something in the Reference Table. Exactly what is this Table used for, and what did you change?

[forrestgump]

I imagine that it could be as simple as a Row insertion between each CopyFromRecords et.

I have tried to add in a blank row etc but I am getting a runtime error 2147467259.

[ADezii]

I have tried to add in a blank row etc but I am getting a runtime error 2147467259.

The only option I can see at this point, is if you would be willing to send me a scaled down version of the Database as an Attachment to an E-Mail, so I can test it locally. If you would like to do this, let me know and I'll send you my E-Maill Address in a Private Message.

[forrestgump]

The only option I can see at this point, is if you would be willing to send me a scaled down version of the Database as an Attachment to an E-Mail, so I can test it locally. If you would like to do this, let me know and I'll send you my E-Maill Address in a Private Message.

Yes please that would be great