By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,028 Members | 1,092 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,028 IT Pros & Developers. It's quick & easy.

Compile Error: User Defined Type

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

Expand|Select|Wrap|Line Numbers
  1.  Public Sub getrs() 
  2. Dim adoconn As ADODB.Connection
  3. Dim adors As ADODB.Recordset
  4. Dim sql As String
  5. Dim filenm As String
  6. sql = "Select * from Table1"
  7. filenm = "R:\HR\HR_System_Reports_Folder\Databases\HeadCount.mdb"
  8. Call GetCn(adoconn, adors, sql, filenm, "", "")
  9. Dim xlsht As Excel.Worksheet
  10. Set xlsht = Sheets("Sheet1")
  11. xlsht.Range("A1").CopyFromRecordset adors
  12. adors.Close
  13. adoconn.Close
  14. Set adors = Nothing
  15. Set adoconn = Nothing
  16. Set xlsht = Nothing
  17. End Sub
  18.  
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?
Dec 7 '07 #1
Share this Question
Share on Google+
13 Replies


Dököll
Expert 100+
P: 2,364
I am currently trying to use the below VBA to import information into excel from access. This VBA is in the excle sheet:-

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub getrs()
  3. Dim adoconn As ADODB.Connection
  4. Dim adors As ADODB.Recordset
  5. Dim sql As String
  6. Dim filenm As String
  7. sql = "Select * from Table1"
  8. filenm = "R:\HR\HR_System_Reports_Folder\Databases\HeadCount.mdb"
  9. Call GetCn(adoconn, adors, sql, filenm, "", "")
  10. Dim xlsht As Excel.Worksheet
  11. Set xlsht = Sheets("Sheet1")
  12. xlsht.Range("A1").CopyFromRecordset adors
  13. adors.Close
  14. adoconn.Close
  15. Set adors = Nothing
  16. Set adoconn = Nothing
  17. Set xlsht = Nothing
  18. End Sub
  19.  
  20.  
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?
Dec 7 '07 #2

P: 34
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.
Dec 7 '07 #3

ADezii
Expert 5K+
P: 8,628
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\HeadCoun t.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?
  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:
    Expand|Select|Wrap|Line Numbers
    1. 'Declarations
    2. Dim adoconn As ADODB.Connection
    3. Dim adors As ADODB.Recordset
    4.  
    5. 'Creation of New Object Instances
    6. Set adoconn = New ADODB.Connection
    7. Set adors = New ADODB.Recordset
  3. The problem may be with the GetCn Routine, namely:
    Expand|Select|Wrap|Line Numbers
    1. GetCn(adoconn, adors, sql, filenm, "", "")
  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.
Dec 8 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
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!
Dec 8 '07 #5

P: 34
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.Connection, ByRef dbrs As ADODB.Recordset, _
sqlstr As String, dbfile As String, usernm As String, pword As String)
Set dbcon = New ADODB.Connection
dbcon.Open "PROVIDER=Microsoft.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.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\HeadCoun t.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

I need to find a way of pulling multiple queries, stacked, with a blank line in between. Any ideas?
Dec 11 '07 #6

ADezii
Expert 5K+
P: 8,628
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.Connection, ByRef dbrs As ADODB.Recordset, _
sqlstr As String, dbfile As String, usernm As String, pword As String)
Set dbcon = New ADODB.Connection
dbcon.Open "PROVIDER=Microsoft.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.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\HeadCoun t.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

I need to find a way of pulling multiple queries, stacked, with a blank line in between. Any ideas?
Please describe in greater detail.
Dec 11 '07 #7

P: 34
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
Dec 13 '07 #8

ADezii
Expert 5K+
P: 8,628
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 CopyFromRecordset.
Dec 13 '07 #9

P: 2
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.Collections.Generic;
using System.ComponentModel;
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()
{
InitializeComponent();
}

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

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

}
}
}


Please help me i really want it to work !!!
Dec 13 '07 #10

ADezii
Expert 5K+
P: 8,628
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.Collections.Generic;
using System.ComponentModel;
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()
{
InitializeComponent();
}

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

private void Form1_Load(object sender, EventArgs e)
{
// Show the open file dialog box.
if (ofdSelectPicture.ShowDialog() == DialogResult.OK)
{
//Load the picture into the picture box.
picShowPicture.Image = Image.FromFile(ofdSelectPicture.FileName);
// Show the name of the file in the form's caption.
this.Text = String.Concat("Picture Viewer (" + ofdSelectPicture.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?
Dec 14 '07 #11

P: 34
I imagine that it could be as simple as a Row insertion between each CopyFromRecordset.
I have tried to add in a blank row etc but I am getting a runtime error 2147467259.
Dec 14 '07 #12

ADezii
Expert 5K+
P: 8,628
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.
Dec 14 '07 #13

P: 34
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
Dec 14 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.