Compile Error: User Defined Type

Originally Posted by forrestgump

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

Originally Posted by Dököll

Are you doing this from Access, or Excel forrestgump?

Originally Posted by 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\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?

Originally Posted by 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!

Originally Posted by forrestgump

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?

Originally Posted by ADezii

Please describe in greater detail.

Originally Posted by forrestgump

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

Originally Posted by 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.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 !!!

Originally Posted by ADezii

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

Originally Posted by forrestgump

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

Originally Posted by ADezii

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.