Importing File Names to Access?

[Wayne]

Applies to Microsoft Access 2003

I have second hard drive installed on my PC which I have designated as a
backup drive for my CD collection, I've managed to copy my CDs using Media
Player and save the files as .wmv files, but now what I would like to do is
import those file names (without the extension) into a Microsoft Access
database - hopefully to save me the time of manually entering the Artist
Name and Track Title data, so I have an easy to use music database which I
can update as I add more CDs to my collection.

I've read on the Internet that it is possible to import file names into
Access using VBA code and although the code was provided, this is not
something I have ever done before and I'm a little naive about adding code
to a database etc.

Does anyone know if this is really possible and what I need to do (in simple
straightforward terms) to achieve this.

Any help, advice, pointers or line by line list of what to do would be
enormously appreciated. Other than creating very simple tables, I'm not by
any stretch of the imagination an expert on Microsoft Access.

Many thanks in advance
Wayne

 

[Klatuu]

First, you will need to create a table to hold the names of you wmv files.
Make your first field an AutoNumber field. It will be one of the data type
options when you create a field. Give it a name something like WMV_PK to
identify it as the primary key. You may need this later.
Then create a text field to hold the the file name. Let's call it
MUSIC_TITLE. Make it long enough to hold your longest file name. That should
be enough for this table for now.

Now, to load the file names into your table, you will need a VBA Sub. to
read the file names and put them in the table:
Sub LoadFileNames
Dim dbf as Database 'Object reference to the database
Dim rst as Recordset 'Object reference to the table
Dim txtFilePath as Text 'Variable to hold the path to the files
Dim txtFileName as Text 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("YourTableNameGoesHere", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "D:\MyMusicFolder\*.wmv"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return an empty
string
'when all matching files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) -4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "YourTableName", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub

You can run this from the VBA immediate window and it will create one record
in your table for each file in the directory you identify.

Now, when you want to add specific track information, it should be in a
related table that will be a child table to your Titles table. You link the
two tables by creating a field in the Track Info table that is the Autonumber
field in your Titles table, but you don't identify it as Autonumber, you
identify it as Number, Long. Then when you add data to the Track Info table,
you will know to which Title it belongs.

Hopefully, this will give you a start.

 

[Wayne]

Thanks for your insight, it is really appreciated - although I seem to be
having a little problem with the code.

I've followed your instructions to the letter and created the table as
specified, then I moved on to the code by selecting 'Tools', 'Macro',
'Visual Basic Editor' from within Access, I copied and pasted the code you
provided and tried to run it, but I receive the following error message:

Compile error:

User-defined type not defined

It highlights this text:: txtFilePath As Text (4th row down in your code)

in a similar fashion as when you drag your cursor over a line of text to
copy it.

and it also highlights this in yellow: Sub LoadFileNames() (1st row in your
code)

Any idea what the problem could be, there's every likelihood that it may be
something I'm doing incorrectly, as I said in my original message, I've
never attempted adding code to an Access database before so it may be just
me being a plum.

Thanks in advance
Wayne

First, you will need to create a table to hold the names of you wmv files.
Make your first field an AutoNumber field. It will be one of the data type
options when you create a field. Give it a name something like WMV_PK to
identify it as the primary key. You may need this later.
Then create a text field to hold the the file name. Let's call it
MUSIC_TITLE. Make it long enough to hold your longest file name. That should
be enough for this table for now.

Now, to load the file names into your table, you will need a VBA Sub. to
read the file names and put them in the table:
Sub LoadFileNames
Dim dbf as Database 'Object reference to the database
Dim rst as Recordset 'Object reference to the table
Dim txtFilePath as Text 'Variable to hold the path to the files
Dim txtFileName as Text 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("YourTableNameGoesHere", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "D:\MyMusicFolder\*.wmv"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return an empty
string
'when all matching files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) -4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "YourTableName", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub

You can run this from the VBA immediate window and it will create one record
in your table for each file in the directory you identify.

Now, when you want to add specific track information, it should be in a
related table that will be a child table to your Titles table. You link the
two tables by creating a field in the Track Info table that is the Autonumber
field in your Titles table, but you don't identify it as Autonumber, you
identify it as Number, Long. Then when you add data to the Track Info table,
you will know to which Title it belongs.

Hopefully, this will give you a start.

Applies to Microsoft Access 2003

I have second hard drive installed on my PC which I have designated as a
backup drive for my CD collection, I've managed to copy my CDs using Media
Player and save the files as .wmv files, but now what I would like to do is
import those file names (without the extension) into a Microsoft Access
database - hopefully to save me the time of manually entering the Artist
Name and Track Title data, so I have an easy to use music database which I
can update as I add more CDs to my collection.

I've read on the Internet that it is possible to import file names into
Access using VBA code and although the code was provided, this is not
something I have ever done before and I'm a little naive about adding code
to a database etc.

Does anyone know if this is really possible and what I need to do (in simple
straightforward terms) to achieve this.

Any help, advice, pointers or line by line list of what to do would be
enormously appreciated. Other than creating very simple tables, I'm not by
any stretch of the imagination an expert on Microsoft Access.

Many thanks in advance
Wayne

 

[Klatuu]

OOps! Monday morning! My brain doesn't come in until Tuesday. in that line
and the next, replace the word "text" with "string"
It is highlighting the first line, because it is trying to compile the code
and found an error in the Sub. That is normal.
BTW, this was untested air code, so there may be issues to work out. Don't
give up, if you have any additional problems, please post back with as much
info as you can, just like this time. Also, if you get an error, the error
number and description would be useful.

Thanks for your insight, it is really appreciated - although I seem to be
having a little problem with the code.

I've followed your instructions to the letter and created the table as
specified, then I moved on to the code by selecting 'Tools', 'Macro',
'Visual Basic Editor' from within Access, I copied and pasted the code you
provided and tried to run it, but I receive the following error message:

Compile error:

User-defined type not defined

It highlights this text:: txtFilePath As Text (4th row down in your code)

in a similar fashion as when you drag your cursor over a line of text to
copy it.

and it also highlights this in yellow: Sub LoadFileNames() (1st row in your
code)

Any idea what the problem could be, there's every likelihood that it may be
something I'm doing incorrectly, as I said in my original message, I've
never attempted adding code to an Access database before so it may be just
me being a plum.

Thanks in advance
Wayne

First, you will need to create a table to hold the names of you wmv files.
Make your first field an AutoNumber field. It will be one of the data type
options when you create a field. Give it a name something like WMV_PK to
identify it as the primary key. You may need this later.
Then create a text field to hold the the file name. Let's call it
MUSIC_TITLE. Make it long enough to hold your longest file name. That should
be enough for this table for now.

Now, to load the file names into your table, you will need a VBA Sub. to
read the file names and put them in the table:
Sub LoadFileNames
Dim dbf as Database 'Object reference to the database
Dim rst as Recordset 'Object reference to the table
Dim txtFilePath as Text 'Variable to hold the path to the files
Dim txtFileName as Text 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("YourTableNameGoesHere", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "D:\MyMusicFolder\*.wmv"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return an empty
string
'when all matching files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) -4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "YourTableName", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub

You can run this from the VBA immediate window and it will create one record
in your table for each file in the directory you identify.

Now, when you want to add specific track information, it should be in a
related table that will be a child table to your Titles table. You link the
two tables by creating a field in the Track Info table that is the Autonumber
field in your Titles table, but you don't identify it as Autonumber, you
identify it as Number, Long. Then when you add data to the Track Info table,
you will know to which Title it belongs.

Hopefully, this will give you a start.

Applies to Microsoft Access 2003

I have second hard drive installed on my PC which I have designated as a
backup drive for my CD collection, I've managed to copy my CDs using Media
Player and save the files as .wmv files, but now what I would like to do is
import those file names (without the extension) into a Microsoft Access
database - hopefully to save me the time of manually entering the Artist
Name and Track Title data, so I have an easy to use music database which I
can update as I add more CDs to my collection.

I've read on the Internet that it is possible to import file names into
Access using VBA code and although the code was provided, this is not
something I have ever done before and I'm a little naive about adding code
to a database etc.

Does anyone know if this is really possible and what I need to do (in simple
straightforward terms) to achieve this.

Any help, advice, pointers or line by line list of what to do would be
enormously appreciated. Other than creating very simple tables, I'm not by
any stretch of the imagination an expert on Microsoft Access.

Many thanks in advance
Wayne

 

[Wayne]

Hi again,
Similar problems this time too unfortunately. I changed the two lines to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and selecting help opens the Help
file with the following text:

You can create your own data types in Visual Basic, but they must be defined
first in a Type...End Type statement or in a properly registered object
library or type library. This error has the following causes and solutions:
a.. You tried to declare a variable or argument with an undefined data
type or you specified an unknown class or object name.
Use the Type statement in a module to define a new data type. If you are
trying to create a reference to a class, the class must be visible to the
project. If you are referring to a class in your program, you must have a
class module of the specified name in your project. Check the spelling of
the type name or name of the object.

b.. The type you want to declare is in another module but has been
declared Private.
Move the definition of the type to a standard module where it can be
Public.

c.. The type is a valid type, but the object library or type library in
which it is defined isn't registered in Visual Basic.
Display the References dialog box, and then select the appropriate object
library or type library. For example, if you don't check the Data Access
Object in the References dialog box, types like Database, Recordset, and
TableDef aren't recognized and references to them in code cause this error.

For additional information, select the item in question and press F1 (in
Windows) or HELP (on the Macintosh).

Hope this information is useful, and thanks again for your input so far

Best Regards,

Wayne

OOps! Monday morning! My brain doesn't come in until Tuesday. in that line
and the next, replace the word "text" with "string"
It is highlighting the first line, because it is trying to compile the code
and found an error in the Sub. That is normal.
BTW, this was untested air code, so there may be issues to work out. Don't
give up, if you have any additional problems, please post back with as much
info as you can, just like this time. Also, if you get an error, the error
number and description would be useful.

Thanks for your insight, it is really appreciated - although I seem to be
having a little problem with the code.

I've followed your instructions to the letter and created the table as
specified, then I moved on to the code by selecting 'Tools', 'Macro',
'Visual Basic Editor' from within Access, I copied and pasted the code you
provided and tried to run it, but I receive the following error message:

Compile error:

User-defined type not defined

It highlights this text:: txtFilePath As Text (4th row down in your code)

in a similar fashion as when you drag your cursor over a line of text to
copy it.

and it also highlights this in yellow: Sub LoadFileNames() (1st row in your
code)

Any idea what the problem could be, there's every likelihood that it may be
something I'm doing incorrectly, as I said in my original message, I've
never attempted adding code to an Access database before so it may be just
me being a plum.

Thanks in advance
Wayne

First, you will need to create a table to hold the names of you wmv files.
Make your first field an AutoNumber field. It will be one of the data type
options when you create a field. Give it a name something like WMV_PK to
identify it as the primary key. You may need this later.
Then create a text field to hold the the file name. Let's call it
MUSIC_TITLE. Make it long enough to hold your longest file name. That should
be enough for this table for now.

Now, to load the file names into your table, you will need a VBA Sub. to
read the file names and put them in the table:
Sub LoadFileNames
Dim dbf as Database 'Object reference to the database
Dim rst as Recordset 'Object reference to the table
Dim txtFilePath as Text 'Variable to hold the path to the files
Dim txtFileName as Text 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("YourTableNameGoesHere", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "D:\MyMusicFolder\*.wmv"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return an empty
string
'when all matching files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) -4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "YourTableName", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub

You can run this from the VBA immediate window and it will create one record
in your table for each file in the directory you identify.

Now, when you want to add specific track information, it should be in a
related table that will be a child table to your Titles table. You

link
the

two tables by creating a field in the Track Info table that is the Autonumber
field in your Titles table, but you don't identify it as Autonumber, you
identify it as Number, Long. Then when you add data to the Track Info table,
you will know to which Title it belongs.

Hopefully, this will give you a start.
:

Applies to Microsoft Access 2003

I have second hard drive installed on my PC which I have designated as a
backup drive for my CD collection, I've managed to copy my CDs using Media
Player and save the files as .wmv files, but now what I would like

to do
is

import those file names (without the extension) into a Microsoft Access
database - hopefully to save me the time of manually entering the Artist
Name and Track Title data, so I have an easy to use music database

which
I

can update as I add more CDs to my collection.

I've read on the Internet that it is possible to import file names into
Access using VBA code and although the code was provided, this is not
something I have ever done before and I'm a little naive about

adding
code

to a database etc.

Does anyone know if this is really possible and what I need to do

(in
simple

straightforward terms) to achieve this.

Any help, advice, pointers or line by line list of what to do would be
enormously appreciated. Other than creating very simple tables, I'm

not
by

any stretch of the imagination an expert on Microsoft Access.

Many thanks in advance
Wayne

 

[Klatuu]

Sorry If I did not make myself clear on the last post. What I meant was to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String

Hi again,
Similar problems this time too unfortunately. I changed the two lines to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and selecting help opens the Help
file with the following text:

You can create your own data types in Visual Basic, but they must be defined
first in a Type...End Type statement or in a properly registered object
library or type library. This error has the following causes and solutions:
a.. You tried to declare a variable or argument with an undefined data
type or you specified an unknown class or object name.
Use the Type statement in a module to define a new data type. If you are
trying to create a reference to a class, the class must be visible to the
project. If you are referring to a class in your program, you must have a
class module of the specified name in your project. Check the spelling of
the type name or name of the object.

b.. The type you want to declare is in another module but has been
declared Private.
Move the definition of the type to a standard module where it can be
Public.

c.. The type is a valid type, but the object library or type library in
which it is defined isn't registered in Visual Basic.
Display the References dialog box, and then select the appropriate object
library or type library. For example, if you don't check the Data Access
Object in the References dialog box, types like Database, Recordset, and
TableDef aren't recognized and references to them in code cause this error.

For additional information, select the item in question and press F1 (in
Windows) or HELP (on the Macintosh).

Hope this information is useful, and thanks again for your input so far

Best Regards,

Wayne

OOps! Monday morning! My brain doesn't come in until Tuesday. in that line
and the next, replace the word "text" with "string"
It is highlighting the first line, because it is trying to compile the code
and found an error in the Sub. That is normal.
BTW, this was untested air code, so there may be issues to work out. Don't
give up, if you have any additional problems, please post back with as much
info as you can, just like this time. Also, if you get an error, the error
number and description would be useful.

Thanks for your insight, it is really appreciated - although I seem to be
having a little problem with the code.

I've followed your instructions to the letter and created the table as
specified, then I moved on to the code by selecting 'Tools', 'Macro',
'Visual Basic Editor' from within Access, I copied and pasted the code you
provided and tried to run it, but I receive the following error message:

Compile error:

User-defined type not defined

It highlights this text:: txtFilePath As Text (4th row down in your code)

in a similar fashion as when you drag your cursor over a line of text to
copy it.

and it also highlights this in yellow: Sub LoadFileNames() (1st row in your
code)

Any idea what the problem could be, there's every likelihood that it may be
something I'm doing incorrectly, as I said in my original message, I've
never attempted adding code to an Access database before so it may be just
me being a plum.

Thanks in advance
Wayne





First, you will need to create a table to hold the names of you wmv
files.
Make your first field an AutoNumber field. It will be one of the data
type
options when you create a field. Give it a name something like WMV_PK to
identify it as the primary key. You may need this later.
Then create a text field to hold the the file name. Let's call it
MUSIC_TITLE. Make it long enough to hold your longest file name. That
should
be enough for this table for now.

Now, to load the file names into your table, you will need a VBA Sub. to
read the file names and put them in the table:
Sub LoadFileNames
Dim dbf as Database 'Object reference to the database
Dim rst as Recordset 'Object reference to the table
Dim txtFilePath as Text 'Variable to hold the path to the files
Dim txtFileName as Text 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("YourTableNameGoesHere", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "D:\MyMusicFolder\*.wmv"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return an
empty
string
'when all matching
files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) -4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "YourTableName", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub

You can run this from the VBA immediate window and it will create one
record
in your table for each file in the directory you identify.

Now, when you want to add specific track information, it should be in a
related table that will be a child table to your Titles table. You link
the
two tables by creating a field in the Track Info table that is the
Autonumber
field in your Titles table, but you don't identify it as Autonumber, you
identify it as Number, Long. Then when you add data to the Track Info
table,
you will know to which Title it belongs.

Hopefully, this will give you a start.
:

Applies to Microsoft Access 2003

I have second hard drive installed on my PC which I have designated as a
backup drive for my CD collection, I've managed to copy my CDs using
Media
Player and save the files as .wmv files, but now what I would like to do
is
import those file names (without the extension) into a Microsoft Access
database - hopefully to save me the time of manually entering the Artist
Name and Track Title data, so I have an easy to use music database which
I
can update as I add more CDs to my collection.

I've read on the Internet that it is possible to import file names into
Access using VBA code and although the code was provided, this is not
something I have ever done before and I'm a little naive about adding
code
to a database etc.

Does anyone know if this is really possible and what I need to do (in
simple
straightforward terms) to achieve this.

Any help, advice, pointers or line by line list of what to do would be
enormously appreciated. Other than creating very simple tables, I'm not
by
any stretch of the imagination an expert on Microsoft Access.

Many thanks in advance
Wayne

 

[Wayne]

I think we may be getting somewhere, the errors from earlier are no longer
present and the only error now is a syntax error which I assume must be
fairly simple, although I have looked at the code and nothing jumps out at
me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then

Bear in mind also I have named the components as follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE

Sorry If I did not make myself clear on the last post. What I meant was to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String

Hi again,
Similar problems this time too unfortunately. I changed the two lines to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and selecting help opens the Help
file with the following text:

You can create your own data types in Visual Basic, but they must be defined
first in a Type...End Type statement or in a properly registered object
library or type library. This error has the following causes and solutions:
a.. You tried to declare a variable or argument with an undefined data
type or you specified an unknown class or object name.
Use the Type statement in a module to define a new data type. If you are
trying to create a reference to a class, the class must be visible to the
project. If you are referring to a class in your program, you must have a
class module of the specified name in your project. Check the spelling of
the type name or name of the object.

b.. The type you want to declare is in another module but has been
declared Private.
Move the definition of the type to a standard module where it can be
Public.

c.. The type is a valid type, but the object library or type library in
which it is defined isn't registered in Visual Basic.
Display the References dialog box, and then select the appropriate object
library or type library. For example, if you don't check the Data Access
Object in the References dialog box, types like Database, Recordset, and
TableDef aren't recognized and references to them in code cause this error.

For additional information, select the item in question and press F1 (in
Windows) or HELP (on the Macintosh).

Hope this information is useful, and thanks again for your input so far

Best Regards,

Wayne

OOps! Monday morning! My brain doesn't come in until Tuesday. in that line
and the next, replace the word "text" with "string"
It is highlighting the first line, because it is trying to compile the code
and found an error in the Sub. That is normal.
BTW, this was untested air code, so there may be issues to work out. Don't
give up, if you have any additional problems, please post back with as much
info as you can, just like this time. Also, if you get an error, the error
number and description would be useful.

:



Thanks for your insight, it is really appreciated - although I seem

to
be

having a little problem with the code.

I've followed your instructions to the letter and created the table as
specified, then I moved on to the code by selecting 'Tools', 'Macro',
'Visual Basic Editor' from within Access, I copied and pasted the

code
you

provided and tried to run it, but I receive the following error message:

Compile error:

User-defined type not defined

It highlights this text:: txtFilePath As Text (4th row down in your code)

in a similar fashion as when you drag your cursor over a line of text to
copy it.

and it also highlights this in yellow: Sub LoadFileNames() (1st row

in
your

code)

Any idea what the problem could be, there's every likelihood that it

may
be

something I'm doing incorrectly, as I said in my original message, I've
never attempted adding code to an Access database before so it may

be
just

me being a plum.

Thanks in advance
Wayne





First, you will need to create a table to hold the names of you wmv
files.
Make your first field an AutoNumber field. It will be one of the data
type
options when you create a field. Give it a name something like

WMV_PK
to

identify it as the primary key. You may need this later.
Then create a text field to hold the the file name. Let's call it
MUSIC_TITLE. Make it long enough to hold your longest file name. That
should
be enough for this table for now.

Now, to load the file names into your table, you will need a VBA

Sub.
to

read the file names and put them in the table:
Sub LoadFileNames
Dim dbf as Database 'Object reference to the database
Dim rst as Recordset 'Object reference to the table
Dim txtFilePath as Text 'Variable to hold the path to the files
Dim txtFileName as Text 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("YourTableNameGoesHere", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "D:\MyMusicFolder\*.wmv"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return an
empty
string
'when all matching
files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) -4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "YourTableName", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub

You can run this from the VBA immediate window and it will create one
record
in your table for each file in the directory you identify.

Now, when you want to add specific track information, it should be

in
a

related table that will be a child table to your Titles table.

You
link

the
two tables by creating a field in the Track Info table that is the
Autonumber
field in your Titles table, but you don't identify it as

Autonumber,
you

identify it as Number, Long. Then when you add data to the Track Info
table,
you will know to which Title it belongs.

Hopefully, this will give you a start.
:

Applies to Microsoft Access 2003

I have second hard drive installed on my PC which I have

designated
as a

backup drive for my CD collection, I've managed to copy my CDs using
Media
Player and save the files as .wmv files, but now what I would

like
to do

is
import those file names (without the extension) into a Microsoft Access
database - hopefully to save me the time of manually entering

the
Artist

Name and Track Title data, so I have an easy to use music

database
which

I
can update as I add more CDs to my collection.

I've read on the Internet that it is possible to import file

names
into

Access using VBA code and although the code was provided, this

is
not

something I have ever done before and I'm a little naive about adding
code
to a database etc.

Does anyone know if this is really possible and what I need to

do
(in

simple
straightforward terms) to achieve this.

Any help, advice, pointers or line by line list of what to do

would
be

enormously appreciated. Other than creating very simple tables,

I'm
not

by
any stretch of the imagination an expert on Microsoft Access.

Many thanks in advance
Wayne

 

[Wayne]

it doesn't seem to like the DLookup bit, its highlighting that part
specifically?

I think we may be getting somewhere, the errors from earlier are no longer
present and the only error now is a syntax error which I assume must be
fairly simple, although I have looked at the code and nothing jumps out at
me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then

Bear in mind also I have named the components as follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE

Sorry If I did not make myself clear on the last post. What I meant was to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String

have

a

seem

to

table

as

row

in

it

may

the

data

return

an

empty
string
'when all matching
files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) -4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "YourTableName", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub

You can run this from the VBA immediate window and it will

create

one

be

in

Track

Info

tables,

I'm

 

[Klatuu]

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then

Sorry, left out the right paren.

There

it doesn't seem to like the DLookup bit, its highlighting that part
specifically?

I think we may be getting somewhere, the errors from earlier are no longer
present and the only error now is a syntax error which I assume must be
fairly simple, although I have looked at the code and nothing jumps out at
me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then

Bear in mind also I have named the components as follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE

Sorry If I did not make myself clear on the last post. What I meant was to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String


:

Hi again,
Similar problems this time too unfortunately. I changed the two lines to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and selecting help opens the Help
file with the following text:

You can create your own data types in Visual Basic, but they must be defined
first in a Type...End Type statement or in a properly registered object
library or type library. This error has the following causes and solutions:
a.. You tried to declare a variable or argument with an undefined data
type or you specified an unknown class or object name.
Use the Type statement in a module to define a new data type. If you are
trying to create a reference to a class, the class must be visible to the
project. If you are referring to a class in your program, you must

have

a
class module of the specified name in your project. Check the spelling of
the type name or name of the object.

b.. The type you want to declare is in another module but has been
declared Private.
Move the definition of the type to a standard module where it can be
Public.

c.. The type is a valid type, but the object library or type library in
which it is defined isn't registered in Visual Basic.
Display the References dialog box, and then select the appropriate object
library or type library. For example, if you don't check the Data Access
Object in the References dialog box, types like Database, Recordset, and
TableDef aren't recognized and references to them in code cause this error.

For additional information, select the item in question and press F1 (in
Windows) or HELP (on the Macintosh).

Hope this information is useful, and thanks again for your input so far

Best Regards,

Wayne






OOps! Monday morning! My brain doesn't come in until Tuesday. in that
line
and the next, replace the word "text" with "string"
It is highlighting the first line, because it is trying to compile the
code
and found an error in the Sub. That is normal.
BTW, this was untested air code, so there may be issues to work out.
Don't
give up, if you have any additional problems, please post back with as
much
info as you can, just like this time. Also, if you get an error, the
error
number and description would be useful.

:



Thanks for your insight, it is really appreciated - although I

seem

to
be
having a little problem with the code.

I've followed your instructions to the letter and created the

table

as
specified, then I moved on to the code by selecting 'Tools', 'Macro',
'Visual Basic Editor' from within Access, I copied and pasted the code
you
provided and tried to run it, but I receive the following error message:

Compile error:

User-defined type not defined

It highlights this text:: txtFilePath As Text (4th row down in your
code)

in a similar fashion as when you drag your cursor over a line of text to
copy it.

and it also highlights this in yellow: Sub LoadFileNames() (1st

row

in
your
code)

Any idea what the problem could be, there's every likelihood that

it

may
be
something I'm doing incorrectly, as I said in my original message, I've
never attempted adding code to an Access database before so it may be
just
me being a plum.

Thanks in advance
Wayne





First, you will need to create a table to hold the names of you wmv
files.
Make your first field an AutoNumber field. It will be one of

the

data
type
options when you create a field. Give it a name something like WMV_PK
to
identify it as the primary key. You may need this later.
Then create a text field to hold the the file name. Let's call it
MUSIC_TITLE. Make it long enough to hold your longest file name. That
should
be enough for this table for now.

Now, to load the file names into your table, you will need a VBA Sub.
to
read the file names and put them in the table:
Sub LoadFileNames
Dim dbf as Database 'Object reference to the database
Dim rst as Recordset 'Object reference to the table
Dim txtFilePath as Text 'Variable to hold the path to the files
Dim txtFileName as Text 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("YourTableNameGoesHere",
dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "D:\MyMusicFolder\*.wmv"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will

return

an
empty
string
'when all matching
files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) -4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "YourTableName", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub

You can run this from the VBA immediate window and it will

create

one
record
in your table for each file in the directory you identify.

Now, when you want to add specific track information, it should

be

in
a
related table that will be a child table to your Titles table. You
link
the
two tables by creating a field in the Track Info table that is the
Autonumber
field in your Titles table, but you don't identify it as Autonumber,
you
identify it as Number, Long. Then when you add data to the

Track

Info
table,
you will know to which Title it belongs.

Hopefully, this will give you a start.
:

Applies to Microsoft Access 2003

I have second hard drive installed on my PC which I have designated
as a
backup drive for my CD collection, I've managed to copy my CDs using
Media
Player and save the files as .wmv files, but now what I would like
to do
is
import those file names (without the extension) into a Microsoft
Access
database - hopefully to save me the time of manually entering the
Artist
Name and Track Title data, so I have an easy to use music database
which
I
can update as I add more CDs to my collection.

 

[Wayne]

I'm probably as confused as you, the right closed bracket does make sense
but its still throwing up a syntax error on that same code:

Compile error:

Syntax error

Is exactly what it says

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then

Sorry, left out the right paren.

There

it doesn't seem to like the DLookup bit, its highlighting that part
specifically?

I think we may be getting somewhere, the errors from earlier are no longer
present and the only error now is a syntax error which I assume must be
fairly simple, although I have looked at the code and nothing jumps out at
me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then

Bear in mind also I have named the components as follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE




Sorry If I did not make myself clear on the last post. What I meant was
to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String


:

Hi again,
Similar problems this time too unfortunately. I changed the two

lines
to

read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and selecting help opens the
Help
file with the following text:

You can create your own data types in Visual Basic, but they must be
defined
first in a Type...End Type statement or in a properly registered object
library or type library. This error has the following causes and
solutions:
a.. You tried to declare a variable or argument with an

undefined
data

type or you specified an unknown class or object name.
Use the Type statement in a module to define a new data type. If you
are
trying to create a reference to a class, the class must be visible to
the
project. If you are referring to a class in your program, you must have
a
class module of the specified name in your project. Check the spelling
of
the type name or name of the object.

b.. The type you want to declare is in another module but has been
declared Private.
Move the definition of the type to a standard module where it can be
Public.

c.. The type is a valid type, but the object library or type library
in
which it is defined isn't registered in Visual Basic.
Display the References dialog box, and then select the appropriate
object
library or type library. For example, if you don't check the Data Access
Object in the References dialog box, types like Database,

Recordset,
and

TableDef aren't recognized and references to them in code cause this
error.

For additional information, select the item in question and press

F1
(in

Windows) or HELP (on the Macintosh).

Hope this information is useful, and thanks again for your input

so
far

Best Regards,

Wayne






OOps! Monday morning! My brain doesn't come in until Tuesday.

in
that

line
and the next, replace the word "text" with "string"
It is highlighting the first line, because it is trying to

compile
the

code
and found an error in the Sub. That is normal.
BTW, this was untested air code, so there may be issues to work out.
Don't
give up, if you have any additional problems, please post back

with
as

much
info as you can, just like this time. Also, if you get an

error,
the

error
number and description would be useful.

:



Thanks for your insight, it is really appreciated - although I seem
to
be
having a little problem with the code.

I've followed your instructions to the letter and created the table
as
specified, then I moved on to the code by selecting 'Tools',
'Macro',
'Visual Basic Editor' from within Access, I copied and pasted the
code
you
provided and tried to run it, but I receive the following error
message:

Compile error:

User-defined type not defined

It highlights this text:: txtFilePath As Text (4th row down in your
code)

in a similar fashion as when you drag your cursor over a line of
text to
copy it.

and it also highlights this in yellow: Sub LoadFileNames()

(1st
row

in
your
code)

Any idea what the problem could be, there's every likelihood

that
it

may
be
something I'm doing incorrectly, as I said in my original message,
I've
never attempted adding code to an Access database before so it may
be
just
me being a plum.

Thanks in advance
Wayne





First, you will need to create a table to hold the names of you
wmv
files.
Make your first field an AutoNumber field. It will be one

of
the

data
type
options when you create a field. Give it a name something like
WMV_PK
to
identify it as the primary key. You may need this later.
Then create a text field to hold the the file name. Let's

call
it

MUSIC_TITLE. Make it long enough to hold your longest file name.
That
should
be enough for this table for now.

Now, to load the file names into your table, you will need a VBA
Sub.
to
read the file names and put them in the table:
Sub LoadFileNames
Dim dbf as Database 'Object reference to the database
Dim rst as Recordset 'Object reference to the table
Dim txtFilePath as Text 'Variable to hold the path to the
files
Dim txtFileName as Text 'Variable to hold individual file
names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("YourTableNameGoesHere",
dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "D:\MyMusicFolder\*.wmv"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return
an
empty
string
'when all
matching
files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) -4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]",

"YourTableName",
_

"[MUSIC_TITLE] = '" & txtFileName & "'" Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub

You can run this from the VBA immediate window and it will create
one
record
in your table for each file in the directory you identify.

Now, when you want to add specific track information, it

should
be

in
a
related table that will be a child table to your Titles table.
You
link
the
two tables by creating a field in the Track Info table that

is
the

Autonumber
field in your Titles table, but you don't identify it as
Autonumber,
you
identify it as Number, Long. Then when you add data to the Track
Info
table,
you will know to which Title it belongs.

Hopefully, this will give you a start.
:

Applies to Microsoft Access 2003

I have second hard drive installed on my PC which I have
designated
as a
backup drive for my CD collection, I've managed to copy my CDs
using
Media
Player and save the files as .wmv files, but now what I would
like
to do
is
import those file names (without the extension) into a Microsoft
Access
database - hopefully to save me the time of manually entering
the
Artist
Name and Track Title data, so I have an easy to use music
database
which
I
can update as I add more CDs to my collection.

 

[Klatuu]

Wayne,
Could you please post back the code as it is now, I am getting lost in the
changes.
Thanks for you patience with my hastily written "air code"

I'm probably as confused as you, the right closed bracket does make sense
but its still throwing up a syntax error on that same code:

Compile error:

Syntax error

Is exactly what it says

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then

Sorry, left out the right paren.

There

it doesn't seem to like the DLookup bit, its highlighting that part
specifically?

I think we may be getting somewhere, the errors from earlier are no longer
present and the only error now is a syntax error which I assume must be
fairly simple, although I have looked at the code and nothing jumps out at
me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then

Bear in mind also I have named the components as follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE




Sorry If I did not make myself clear on the last post. What I meant was
to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String


:

Hi again,
Similar problems this time too unfortunately. I changed the two lines
to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and selecting help opens the
Help
file with the following text:

You can create your own data types in Visual Basic, but they must be
defined
first in a Type...End Type statement or in a properly registered
object
library or type library. This error has the following causes and
solutions:
a.. You tried to declare a variable or argument with an undefined
data
type or you specified an unknown class or object name.
Use the Type statement in a module to define a new data type. If you
are
trying to create a reference to a class, the class must be visible to
the
project. If you are referring to a class in your program, you must
have
a
class module of the specified name in your project. Check the spelling
of
the type name or name of the object.

b.. The type you want to declare is in another module but has been
declared Private.
Move the definition of the type to a standard module where it can be
Public.

c.. The type is a valid type, but the object library or type library
in
which it is defined isn't registered in Visual Basic.
Display the References dialog box, and then select the appropriate
object
library or type library. For example, if you don't check the Data
Access
Object in the References dialog box, types like Database, Recordset,
and
TableDef aren't recognized and references to them in code cause this
error.

For additional information, select the item in question and press F1
(in
Windows) or HELP (on the Macintosh).

Hope this information is useful, and thanks again for your input so
far

Best Regards,

Wayne






OOps! Monday morning! My brain doesn't come in until Tuesday. in
that
line
and the next, replace the word "text" with "string"
It is highlighting the first line, because it is trying to compile
the
code
and found an error in the Sub. That is normal.
BTW, this was untested air code, so there may be issues to work out.
Don't
give up, if you have any additional problems, please post back with
as
much
info as you can, just like this time. Also, if you get an error,
the
error
number and description would be useful.

:



Thanks for your insight, it is really appreciated - although I
seem
to
be
having a little problem with the code.

I've followed your instructions to the letter and created the
table
as
specified, then I moved on to the code by selecting 'Tools',
'Macro',
'Visual Basic Editor' from within Access, I copied and pasted the
code
you
provided and tried to run it, but I receive the following error
message:

Compile error:

User-defined type not defined

It highlights this text:: txtFilePath As Text (4th row down in
your
code)

in a similar fashion as when you drag your cursor over a line of
text to
copy it.

and it also highlights this in yellow: Sub LoadFileNames() (1st
row
in
your
code)

Any idea what the problem could be, there's every likelihood that
it
may
be
something I'm doing incorrectly, as I said in my original message,
I've
never attempted adding code to an Access database before so it may
be
just
me being a plum.

Thanks in advance
Wayne





First, you will need to create a table to hold the names of you
wmv
files.
Make your first field an AutoNumber field. It will be one of
the
data
type
options when you create a field. Give it a name something like
WMV_PK
to
identify it as the primary key. You may need this later.
Then create a text field to hold the the file name. Let's call
it
MUSIC_TITLE. Make it long enough to hold your longest file name.
That
should
be enough for this table for now.

Now, to load the file names into your table, you will need a VBA
Sub.
to
read the file names and put them in the table:
Sub LoadFileNames
Dim dbf as Database 'Object reference to the database
Dim rst as Recordset 'Object reference to the table
Dim txtFilePath as Text 'Variable to hold the path to the
files
Dim txtFileName as Text 'Variable to hold individual file
names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("YourTableNameGoesHere",
dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "D:\MyMusicFolder\*.wmv"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will
return
an
empty
string
'when all
matching
files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) -4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]",

 

[Wayne]

The full code, as it now appears in my Access database reads as follows:

Option Compare Database

Sub LoadFileNames()
Dim dbf As Database 'Object reference to the database
Dim rst As Recordset 'Object reference to the table
Dim txtFilePath As String 'Variable to hold the path to the files
Dim txtFileName As String 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("musiclist", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "G:\Music"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return an empty
string
'when all matching files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) - 4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub


Sorry if this is starting to annoy you, I'm really grateful for your help
though

Kind Regards,
Wayne

Wayne,
Could you please post back the code as it is now, I am getting lost in the
changes.
Thanks for you patience with my hastily written "air code"

I'm probably as confused as you, the right closed bracket does make sense
but its still throwing up a syntax error on that same code:

Compile error:

Syntax error

Is exactly what it says

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then

Sorry, left out the right paren.

There

:

it doesn't seem to like the DLookup bit, its highlighting that part
specifically?

I think we may be getting somewhere, the errors from earlier are

no
longer

present and the only error now is a syntax error which I assume

must
be

fairly simple, although I have looked at the code and nothing

jumps
out at

me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then

Bear in mind also I have named the components as follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE




Sorry If I did not make myself clear on the last post. What I

meant
was

to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String


:

Hi again,
Similar problems this time too unfortunately. I changed the

two
lines

to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and selecting help

opens
the

Help
file with the following text:

You can create your own data types in Visual Basic, but they

must
be

defined
first in a Type...End Type statement or in a properly registered
object
library or type library. This error has the following causes and
solutions:
a.. You tried to declare a variable or argument with an undefined
data
type or you specified an unknown class or object name.
Use the Type statement in a module to define a new data

type. If
you

are
trying to create a reference to a class, the class must be

visible
to

the
project. If you are referring to a class in your program, you must
have
a
class module of the specified name in your project. Check the spelling
of
the type name or name of the object.

b.. The type you want to declare is in another module but

has
been

declared Private.
Move the definition of the type to a standard module where

it
can be

Public.

c.. The type is a valid type, but the object library or type library
in
which it is defined isn't registered in Visual Basic.
Display the References dialog box, and then select the appropriate
object
library or type library. For example, if you don't check the Data
Access
Object in the References dialog box, types like Database, Recordset,
and
TableDef aren't recognized and references to them in code

cause
this

error.

For additional information, select the item in question and

press
F1

(in
Windows) or HELP (on the Macintosh).

Hope this information is useful, and thanks again for your

input
so

far

Best Regards,

Wayne






OOps! Monday morning! My brain doesn't come in until

Tuesday.
in

that
line
and the next, replace the word "text" with "string"
It is highlighting the first line, because it is trying to compile
the
code
and found an error in the Sub. That is normal.
BTW, this was untested air code, so there may be issues to

work
out.

Don't
give up, if you have any additional problems, please post

back
with

as
much
info as you can, just like this time. Also, if you get an error,
the
error
number and description would be useful.

:



Thanks for your insight, it is really appreciated - although I
seem
to
be
having a little problem with the code.

I've followed your instructions to the letter and created the
table
as
specified, then I moved on to the code by selecting 'Tools',
'Macro',
'Visual Basic Editor' from within Access, I copied and

pasted
the

code
you
provided and tried to run it, but I receive the following error
message:

Compile error:

User-defined type not defined

It highlights this text:: txtFilePath As Text (4th row down in
your
code)

in a similar fashion as when you drag your cursor over a

line
of

text to
copy it.

and it also highlights this in yellow: Sub LoadFileNames() (1st
row
in
your
code)

Any idea what the problem could be, there's every

likelihood
that

it
may
be
something I'm doing incorrectly, as I said in my original message,
I've
never attempted adding code to an Access database before

so it
may

be
just
me being a plum.

Thanks in advance
Wayne





First, you will need to create a table to hold the

names of
you

wmv
files.
Make your first field an AutoNumber field. It will be

one
of

the
data
type
options when you create a field. Give it a name

something
like

WMV_PK
to
identify it as the primary key. You may need this later.
Then create a text field to hold the the file name.

Let's
call

it
MUSIC_TITLE. Make it long enough to hold your longest

file
name.

That
should
be enough for this table for now.

Now, to load the file names into your table, you will

need a
VBA

Sub.
to
read the file names and put them in the table:
Sub LoadFileNames
Dim dbf as Database 'Object reference to the database
Dim rst as Recordset 'Object reference to the table
Dim txtFilePath as Text 'Variable to hold the

path to
the

files
Dim txtFileName as Text 'Variable to hold

individual
file

names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("YourTableNameGoesHere",
dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "D:\MyMusicFolder\*.wmv"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will
return
an
empty
string
'when all
matching
files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) -4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]",

 

[Klatuu]

Be glad when Monday is over. Another stupid left out the parens problem:
If Not IsNull(DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'")) Then

The full code, as it now appears in my Access database reads as follows:

Option Compare Database

Sub LoadFileNames()
Dim dbf As Database 'Object reference to the database
Dim rst As Recordset 'Object reference to the table
Dim txtFilePath As String 'Variable to hold the path to the files
Dim txtFileName As String 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("musiclist", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "G:\Music"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return an empty
string
'when all matching files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) - 4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub


Sorry if this is starting to annoy you, I'm really grateful for your help
though

Kind Regards,
Wayne

Wayne,
Could you please post back the code as it is now, I am getting lost in the
changes.
Thanks for you patience with my hastily written "air code"

I'm probably as confused as you, the right closed bracket does make sense
but its still throwing up a syntax error on that same code:

Compile error:

Syntax error

Is exactly what it says



If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then

Sorry, left out the right paren.

There

:

it doesn't seem to like the DLookup bit, its highlighting that part
specifically?

I think we may be getting somewhere, the errors from earlier are no
longer
present and the only error now is a syntax error which I assume must
be
fairly simple, although I have looked at the code and nothing jumps
out at
me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then

Bear in mind also I have named the components as follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE




Sorry If I did not make myself clear on the last post. What I meant
was
to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String


:

Hi again,
Similar problems this time too unfortunately. I changed the two
lines
to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and selecting help opens
the
Help
file with the following text:

You can create your own data types in Visual Basic, but they must
be
defined
first in a Type...End Type statement or in a properly registered
object
library or type library. This error has the following causes and
solutions:
a.. You tried to declare a variable or argument with an
undefined
data
type or you specified an unknown class or object name.
Use the Type statement in a module to define a new data type. If
you
are
trying to create a reference to a class, the class must be visible
to
the
project. If you are referring to a class in your program, you must
have
a
class module of the specified name in your project. Check the
spelling
of
the type name or name of the object.

b.. The type you want to declare is in another module but has
been
declared Private.
Move the definition of the type to a standard module where it
can be
Public.

c.. The type is a valid type, but the object library or type
library
in
which it is defined isn't registered in Visual Basic.
Display the References dialog box, and then select the
appropriate
object
library or type library. For example, if you don't check the Data
Access
Object in the References dialog box, types like Database,
Recordset,
and
TableDef aren't recognized and references to them in code cause
this
error.

For additional information, select the item in question and press
F1
(in
Windows) or HELP (on the Macintosh).

Hope this information is useful, and thanks again for your input
so
far

Best Regards,

Wayne






OOps! Monday morning! My brain doesn't come in until Tuesday.
in
that
line
and the next, replace the word "text" with "string"
It is highlighting the first line, because it is trying to
compile
the
code
and found an error in the Sub. That is normal.
BTW, this was untested air code, so there may be issues to work
out.
Don't
give up, if you have any additional problems, please post back
with
as
much
info as you can, just like this time. Also, if you get an
error,
the
error
number and description would be useful.

:



Thanks for your insight, it is really appreciated - although I
seem
to
be
having a little problem with the code.

I've followed your instructions to the letter and created the
table
as
specified, then I moved on to the code by selecting 'Tools',
'Macro',
'Visual Basic Editor' from within Access, I copied and pasted
the
code
you
provided and tried to run it, but I receive the following
error
message:

Compile error:

User-defined type not defined

It highlights this text:: txtFilePath As Text (4th row down in
your
code)

in a similar fashion as when you drag your cursor over a line
of
text to
copy it.

and it also highlights this in yellow: Sub LoadFileNames()
(1st
row
in
your
code)

Any idea what the problem could be, there's every likelihood
that

 

[Wayne]

You'll be pleased to know that has stopped any errors from reporting so it
does appear to have solved the problem, I've tried running the code by
selecting the Run menu and Run Sub/UserForm but alas nothing happens. I've
checked the database table and I expected it to populate with entries but so
far at least nothing has appeared.

I won't trouble you for any more assistance though as you have put far too
much help into my little problem and i feel a little guilty for taking up
all your time, if I could buy you a beer I would, I really am grateful.

Thanks for a sterling effort nonetheless

Regards,
Wayne

Be glad when Monday is over. Another stupid left out the parens problem:
If Not IsNull(DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'")) Then

The full code, as it now appears in my Access database reads as follows:

Option Compare Database

Sub LoadFileNames()
Dim dbf As Database 'Object reference to the database
Dim rst As Recordset 'Object reference to the table
Dim txtFilePath As String 'Variable to hold the path to the files
Dim txtFileName As String 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("musiclist", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "G:\Music"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return an empty
string
'when all matching files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) - 4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub


Sorry if this is starting to annoy you, I'm really grateful for your help
though

Kind Regards,
Wayne

Wayne,
Could you please post back the code as it is now, I am getting lost in the
changes.
Thanks for you patience with my hastily written "air code"

:

I'm probably as confused as you, the right closed bracket does make sense
but its still throwing up a syntax error on that same code:

Compile error:

Syntax error

Is exactly what it says



If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then

Sorry, left out the right paren.

There

:

it doesn't seem to like the DLookup bit, its highlighting that part
specifically?

I think we may be getting somewhere, the errors from earlier

are
no

longer
present and the only error now is a syntax error which I

assume
must

be
fairly simple, although I have looked at the code and nothing jumps
out at
me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then

Bear in mind also I have named the components as follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE




Sorry If I did not make myself clear on the last post. What

I
meant

was
to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String


:

Hi again,
Similar problems this time too unfortunately. I changed

the
two

lines
to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and selecting

help
opens

the
Help
file with the following text:

You can create your own data types in Visual Basic, but

they
must

be
defined
first in a Type...End Type statement or in a properly registered
object
library or type library. This error has the following

causes
and

solutions:
a.. You tried to declare a variable or argument with an
undefined
data
type or you specified an unknown class or object name.
Use the Type statement in a module to define a new data type. If
you
are
trying to create a reference to a class, the class must be visible
to
the
project. If you are referring to a class in your program,

you
must

have
a
class module of the specified name in your project. Check the
spelling
of
the type name or name of the object.

b.. The type you want to declare is in another module

but
has

been
declared Private.
Move the definition of the type to a standard module

where
it

can be
Public.

c.. The type is a valid type, but the object library or type
library
in
which it is defined isn't registered in Visual Basic.
Display the References dialog box, and then select the
appropriate
object
library or type library. For example, if you don't check

the
Data

Access
Object in the References dialog box, types like Database,
Recordset,
and
TableDef aren't recognized and references to them in code cause
this
error.

For additional information, select the item in question

and
press

F1
(in
Windows) or HELP (on the Macintosh).

Hope this information is useful, and thanks again for your input
so
far

Best Regards,

Wayne






OOps! Monday morning! My brain doesn't come in until Tuesday.
in
that
line
and the next, replace the word "text" with "string"
It is highlighting the first line, because it is trying to
compile
the
code
and found an error in the Sub. That is normal.
BTW, this was untested air code, so there may be issues

to
work

out.
Don't
give up, if you have any additional problems, please

post
back

with
as
much
info as you can, just like this time. Also, if you get an
error,
the
error
number and description would be useful.

:



Thanks for your insight, it is really appreciated - although I
seem
to
be
having a little problem with the code.

I've followed your instructions to the letter and

created
the

table
as
specified, then I moved on to the code by selecting 'Tools',
'Macro',
'Visual Basic Editor' from within Access, I copied and pasted
the
code
you
provided and tried to run it, but I receive the following
error
message:

Compile error:

User-defined type not defined

It highlights this text:: txtFilePath As Text (4th row down in
your
code)

in a similar fashion as when you drag your cursor over

a
line

of
text to
copy it.

and it also highlights this in yellow: Sub LoadFileNames()
(1st
row
in
your
code)

Any idea what the problem could be, there's every likelihood
that

 

[Klatuu]

Not to worry, I want to see it through. To run it, open the vb editor. You
can do that with alt F11. Then type in the same of the sub LoadFileNames.
It should run and put the names in your table.
Please let me know if that helps.

You'll be pleased to know that has stopped any errors from reporting so it
does appear to have solved the problem, I've tried running the code by
selecting the Run menu and Run Sub/UserForm but alas nothing happens. I've
checked the database table and I expected it to populate with entries but so
far at least nothing has appeared.

I won't trouble you for any more assistance though as you have put far too
much help into my little problem and i feel a little guilty for taking up
all your time, if I could buy you a beer I would, I really am grateful.

Thanks for a sterling effort nonetheless

Regards,
Wayne

Be glad when Monday is over. Another stupid left out the parens problem:
If Not IsNull(DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'")) Then

The full code, as it now appears in my Access database reads as follows:

Option Compare Database

Sub LoadFileNames()
Dim dbf As Database 'Object reference to the database
Dim rst As Recordset 'Object reference to the table
Dim txtFilePath As String 'Variable to hold the path to the files
Dim txtFileName As String 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("musiclist", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "G:\Music"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return an empty
string
'when all matching files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) - 4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub


Sorry if this is starting to annoy you, I'm really grateful for your help
though

Kind Regards,
Wayne



Wayne,
Could you please post back the code as it is now, I am getting lost in the
changes.
Thanks for you patience with my hastily written "air code"

:

I'm probably as confused as you, the right closed bracket does make
sense
but its still throwing up a syntax error on that same code:

Compile error:

Syntax error

Is exactly what it says



If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then

Sorry, left out the right paren.

There

:

it doesn't seem to like the DLookup bit, its highlighting that part
specifically?

I think we may be getting somewhere, the errors from earlier are
no
longer
present and the only error now is a syntax error which I assume
must
be
fairly simple, although I have looked at the code and nothing
jumps
out at
me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then

Bear in mind also I have named the components as follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE




Sorry If I did not make myself clear on the last post. What I
meant
was
to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String


:

Hi again,
Similar problems this time too unfortunately. I changed the
two
lines
to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and selecting help
opens
the
Help
file with the following text:

You can create your own data types in Visual Basic, but they
must
be
defined
first in a Type...End Type statement or in a properly
registered
object
library or type library. This error has the following causes
and
solutions:
a.. You tried to declare a variable or argument with an
undefined
data
type or you specified an unknown class or object name.
Use the Type statement in a module to define a new data
type. If
you
are
trying to create a reference to a class, the class must be
visible
to
the
project. If you are referring to a class in your program, you
must
have
a
class module of the specified name in your project. Check the
spelling
of
the type name or name of the object.

b.. The type you want to declare is in another module but
has
been
declared Private.
Move the definition of the type to a standard module where
it
can be
Public.

c.. The type is a valid type, but the object library or type
library
in
which it is defined isn't registered in Visual Basic.
Display the References dialog box, and then select the
appropriate
object
library or type library. For example, if you don't check the
Data
Access
Object in the References dialog box, types like Database,
Recordset,
and
TableDef aren't recognized and references to them in code
cause
this
error.

For additional information, select the item in question and
press
F1
(in
Windows) or HELP (on the Macintosh).

Hope this information is useful, and thanks again for your
input
so
far

Best Regards,

Wayne






OOps! Monday morning! My brain doesn't come in until
Tuesday.
in
that
line
and the next, replace the word "text" with "string"
It is highlighting the first line, because it is trying to
compile
the
code
and found an error in the Sub. That is normal.
BTW, this was untested air code, so there may be issues to
work
out.
Don't
give up, if you have any additional problems, please post
back
with
as
much
info as you can, just like this time. Also, if you get an
error,
the
error
number and description would be useful.

:

 

[Wayne]

Please excuse my ignorance if I'm misunderstanding what you're saying - I
opened vb editor but where do I type the name of the sub "LoadFileNames"?

By opening the vb editor it brings me back to where I was before with the
original code and I already tried running that, eventhough its no longer
giving any errors, I still cant get it to populate the table in the current
database.

Am I misunderstanding your instructions?

Not to worry, I want to see it through. To run it, open the vb editor. You
can do that with alt F11. Then type in the same of the sub LoadFileNames.
It should run and put the names in your table.
Please let me know if that helps.

You'll be pleased to know that has stopped any errors from reporting so it
does appear to have solved the problem, I've tried running the code by
selecting the Run menu and Run Sub/UserForm but alas nothing happens. I've
checked the database table and I expected it to populate with entries but so
far at least nothing has appeared.

I won't trouble you for any more assistance though as you have put far too
much help into my little problem and i feel a little guilty for taking up
all your time, if I could buy you a beer I would, I really am grateful.

Thanks for a sterling effort nonetheless

Regards,
Wayne

Be glad when Monday is over. Another stupid left out the parens problem:
If Not IsNull(DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'")) Then


:

The full code, as it now appears in my Access database reads as follows:

Option Compare Database

Sub LoadFileNames()
Dim dbf As Database 'Object reference to the database
Dim rst As Recordset 'Object reference to the table
Dim txtFilePath As String 'Variable to hold the path to the files
Dim txtFileName As String 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("musiclist", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "G:\Music"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return

an
empty

string
'when all

matching
files

have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) - 4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub


Sorry if this is starting to annoy you, I'm really grateful for your help
though

Kind Regards,
Wayne



Wayne,
Could you please post back the code as it is now, I am getting

lost in
the

changes.
Thanks for you patience with my hastily written "air code"

:

I'm probably as confused as you, the right closed bracket does make
sense
but its still throwing up a syntax error on that same code:

Compile error:

Syntax error

Is exactly what it says



If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then

Sorry, left out the right paren.

There

:

it doesn't seem to like the DLookup bit, its highlighting

that
part

specifically?

I think we may be getting somewhere, the errors from

earlier
are

no
longer
present and the only error now is a syntax error which I assume
must
be
fairly simple, although I have looked at the code and nothing
jumps
out at
me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then

Bear in mind also I have named the components as follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE




Sorry If I did not make myself clear on the last post.

What
I

meant
was
to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String


:

Hi again,
Similar problems this time too unfortunately. I

changed
the

two
lines
to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and

selecting
help

opens
the
Help
file with the following text:

You can create your own data types in Visual Basic,

but
they

must
be
defined
first in a Type...End Type statement or in a properly
registered
object
library or type library. This error has the following causes
and
solutions:
a.. You tried to declare a variable or argument with an
undefined
data
type or you specified an unknown class or object name.
Use the Type statement in a module to define a new data
type. If
you
are
trying to create a reference to a class, the class must be
visible
to
the
project. If you are referring to a class in your

program,
you

must
have
a
class module of the specified name in your project.

Check
the

spelling
of
the type name or name of the object.

b.. The type you want to declare is in another

module
but

has
been
declared Private.
Move the definition of the type to a standard module where
it
can be
Public.

c.. The type is a valid type, but the object library

or
type

library
in
which it is defined isn't registered in Visual Basic.
Display the References dialog box, and then select the
appropriate
object
library or type library. For example, if you don't

check
the

Data
Access
Object in the References dialog box, types like Database,
Recordset,
and
TableDef aren't recognized and references to them in code
cause
this
error.

For additional information, select the item in

question
and

press
F1
(in
Windows) or HELP (on the Macintosh).

Hope this information is useful, and thanks again for your
input
so
far

Best Regards,

Wayne






OOps! Monday morning! My brain doesn't come in until
Tuesday.
in
that
line
and the next, replace the word "text" with "string"
It is highlighting the first line, because it is

trying
to

compile
the
code
and found an error in the Sub. That is normal.
BTW, this was untested air code, so there may be

issues
to

work
out.
Don't
give up, if you have any additional problems, please post
back
with
as
much
info as you can, just like this time. Also, if you

get
an

error,
the
error
number and description would be useful.

:

 

[Wayne]

If you think it might help, I could always email you the database as I
currently have it. You may be able to see the problem much easier if you're
looking at what I'm looking at?

Just a thought if you think it might help.

Regards,
Wayne

Please excuse my ignorance if I'm misunderstanding what you're saying - I
opened vb editor but where do I type the name of the sub "LoadFileNames"?

By opening the vb editor it brings me back to where I was before with the
original code and I already tried running that, eventhough its no longer
giving any errors, I still cant get it to populate the table in the current
database.

Am I misunderstanding your instructions?

Not to worry, I want to see it through. To run it, open the vb editor. You
can do that with alt F11. Then type in the same of the sub LoadFileNames.
It should run and put the names in your table.
Please let me know if that helps.

so

it

does appear to have solved the problem, I've tried running the code by
selecting the Run menu and Run Sub/UserForm but alas nothing happens. I've
checked the database table and I expected it to populate with entries but so
far at least nothing has appeared.

I won't trouble you for any more assistance though as you have put far too
much help into my little problem and i feel a little guilty for taking up
all your time, if I could buy you a beer I would, I really am grateful.

Thanks for a sterling effort nonetheless

Regards,
Wayne


Be glad when Monday is over. Another stupid left out the parens problem:
If Not IsNull(DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'")) Then


:

The full code, as it now appears in my Access database reads as follows:

Option Compare Database

Sub LoadFileNames()
Dim dbf As Database 'Object reference to the database
Dim rst As Recordset 'Object reference to the table
Dim txtFilePath As String 'Variable to hold the path to the files
Dim txtFileName As String 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("musiclist", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "G:\Music"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return an
empty
string
'when all matching
files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) - 4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub


Sorry if this is starting to annoy you, I'm really grateful for your
help
though

Kind Regards,
Wayne



Wayne,
Could you please post back the code as it is now, I am getting lost in
the
changes.
Thanks for you patience with my hastily written "air code"

:

I'm probably as confused as you, the right closed bracket does make
sense
but its still throwing up a syntax error on that same code:

Compile error:

Syntax error

Is exactly what it says



If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then

Sorry, left out the right paren.

There

:

it doesn't seem to like the DLookup bit, its highlighting that
part
specifically?

I think we may be getting somewhere, the errors from earlier
are
no
longer
present and the only error now is a syntax error which I
assume
must
be
fairly simple, although I have looked at the code and nothing
jumps
out at
me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then

Bear in mind also I have named the components as follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE




Sorry If I did not make myself clear on the last post. What
I
meant
was
to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String


:

Hi again,
Similar problems this time too unfortunately. I changed
the
two
lines
to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and selecting
help
opens
the
Help
file with the following text:

You can create your own data types in Visual Basic, but
they
must
be
defined
first in a Type...End Type statement or in a properly
registered
object
library or type library. This error has the following
causes
and
solutions:
a.. You tried to declare a variable or argument

with

an

library

or

for

your

you

get

 

[Klatuu]

The place to run it is in the Immediate window in the VB editor. It should
be a small window just below the editor window. If yours is not showing,
click View->Immediate Window on the main menu. Type it in there.
I would not mind taking a look at your database; however, I have tried that
before, and where I work always seems to block zip files and mdb files I have
tried to receive before.

If you think it might help, I could always email you the database as I
currently have it. You may be able to see the problem much easier if you're
looking at what I'm looking at?

Just a thought if you think it might help.

Regards,
Wayne

Please excuse my ignorance if I'm misunderstanding what you're saying - I
opened vb editor but where do I type the name of the sub "LoadFileNames"?

By opening the vb editor it brings me back to where I was before with the
original code and I already tried running that, eventhough its no longer
giving any errors, I still cant get it to populate the table in the current
database.

Am I misunderstanding your instructions?

Not to worry, I want to see it through. To run it, open the vb editor. You
can do that with alt F11. Then type in the same of the sub LoadFileNames.
It should run and put the names in your table.
Please let me know if that helps.

:

You'll be pleased to know that has stopped any errors from reporting

so

it
does appear to have solved the problem, I've tried running the code by
selecting the Run menu and Run Sub/UserForm but alas nothing happens. I've
checked the database table and I expected it to populate with entries but so
far at least nothing has appeared.

I won't trouble you for any more assistance though as you have put far too
much help into my little problem and i feel a little guilty for taking up
all your time, if I could buy you a beer I would, I really am grateful.

Thanks for a sterling effort nonetheless

Regards,
Wayne


Be glad when Monday is over. Another stupid left out the parens problem:
If Not IsNull(DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'")) Then


:

The full code, as it now appears in my Access database reads as follows:

Option Compare Database

Sub LoadFileNames()
Dim dbf As Database 'Object reference to the database
Dim rst As Recordset 'Object reference to the table
Dim txtFilePath As String 'Variable to hold the path to the files
Dim txtFileName As String 'Variable to hold individual file names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("musiclist", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "G:\Music"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return an
empty
string
'when all matching
files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) - 4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub


Sorry if this is starting to annoy you, I'm really grateful for your
help
though

Kind Regards,
Wayne



Wayne,
Could you please post back the code as it is now, I am getting lost in
the
changes.
Thanks for you patience with my hastily written "air code"

:

I'm probably as confused as you, the right closed bracket does make
sense
but its still throwing up a syntax error on that same code:

Compile error:

Syntax error

Is exactly what it says



If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then

Sorry, left out the right paren.

There

:

it doesn't seem to like the DLookup bit, its highlighting that
part
specifically?

I think we may be getting somewhere, the errors from earlier
are
no
longer
present and the only error now is a syntax error which I
assume
must
be
fairly simple, although I have looked at the code and nothing
jumps
out at
me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'" Then

Bear in mind also I have named the components as follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE




Sorry If I did not make myself clear on the last post. What
I
meant
was
to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String


:

Hi again,
Similar problems this time too unfortunately. I changed
the
two
lines
to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and selecting
help
opens
the
Help
file with the following text:

You can create your own data types in Visual Basic, but
they
must
be
defined
first in a Type...End Type statement or in a properly
registered
object
library or type library. This error has the following
causes
and
solutions:
a.. You tried to declare a variable or argument

with

an
undefined
data
type or you specified an unknown class or object name.
Use the Type statement in a module to define a new data
type. If
you
are
trying to create a reference to a class, the class must be
visible
to
the
project. If you are referring to a class in your program,
you
must
have
a
class module of the specified name in your project. Check
the
spelling
of
the type name or name of the object.

b.. The type you want to declare is in another module
but
has
been
declared Private.
Move the definition of the type to a standard module
where
it
can be
Public.

 

[Wayne]

I think this one has defeated us both, I typed "Call LoadFileNames()" into
the immediate window but nothing happens at all, I get no errors which I
suppose is a bonus but nothing populates the database. If I try "Sub
LoadFileNames()" or "Call Sub LoadFileNames()" it throws up errors, and not
being a coding person at all, its very difficult for me to look over the
code and see what might be wrong.

Unless you can see anything obvious I think its time to call it a day and
I'll see if I can find another method to achieve the end goal. But I
appreciate your input a great deal.

Wayne

The place to run it is in the Immediate window in the VB editor. It should
be a small window just below the editor window. If yours is not showing,
click View->Immediate Window on the main menu. Type it in there.
I would not mind taking a look at your database; however, I have tried that
before, and where I work always seems to block zip files and mdb files I have
tried to receive before.

If you think it might help, I could always email you the database as I
currently have it. You may be able to see the problem much easier if you're
looking at what I'm looking at?

Just a thought if you think it might help.

Regards,
Wayne

Please excuse my ignorance if I'm misunderstanding what you're saying - I
opened vb editor but where do I type the name of the sub "LoadFileNames"?

By opening the vb editor it brings me back to where I was before with the
original code and I already tried running that, eventhough its no longer
giving any errors, I still cant get it to populate the table in the current
database.

Am I misunderstanding your instructions?



Not to worry, I want to see it through. To run it, open the vb editor.
You
can do that with alt F11. Then type in the same of the sub LoadFileNames.
It should run and put the names in your table.
Please let me know if that helps.

:

You'll be pleased to know that has stopped any errors from

reporting
so

it
does appear to have solved the problem, I've tried running the code by
selecting the Run menu and Run Sub/UserForm but alas nothing happens.
I've
checked the database table and I expected it to populate with entries
but so
far at least nothing has appeared.

I won't trouble you for any more assistance though as you have put far
too
much help into my little problem and i feel a little guilty for taking
up
all your time, if I could buy you a beer I would, I really am grateful.

Thanks for a sterling effort nonetheless

Regards,
Wayne


Be glad when Monday is over. Another stupid left out the parens
problem:
If Not IsNull(DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'")) Then


:

The full code, as it now appears in my Access database reads as
follows:

Option Compare Database

Sub LoadFileNames()
Dim dbf As Database 'Object reference to the database
Dim rst As Recordset 'Object reference to the table
Dim txtFilePath As String 'Variable to hold the path to the
files
Dim txtFileName As String 'Variable to hold individual file
names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("musiclist", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "G:\Music"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return
an
empty
string
'when all
matching
files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) - 4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub


Sorry if this is starting to annoy you, I'm really grateful

for
your

help
though

Kind Regards,
Wayne



Wayne,
Could you please post back the code as it is now, I am getting
lost in
the
changes.
Thanks for you patience with my hastily written "air code"

:

I'm probably as confused as you, the right closed bracket does
make
sense
but its still throwing up a syntax error on that same code:

Compile error:

Syntax error

Is exactly what it says



If Not IsNull DLookup("[MUSIC_TITLE]",

"musiclist",
_

"[MUSIC_TITLE] = '" & txtFileName &

"'")
Then

Sorry, left out the right paren.

There

:

it doesn't seem to like the DLookup bit, its highlighting
that
part
specifically?

message
I think we may be getting somewhere, the errors from
earlier
are
no
longer
present and the only error now is a syntax error which I
assume
must
be
fairly simple, although I have looked at the code and
nothing
jumps
out at
me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]",
"musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'"
Then

Bear in mind also I have named the components as follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE




message
Sorry If I did not make myself clear on the last post.
What
I
meant
was
to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String


:

Hi again,
Similar problems this time too unfortunately. I
changed
the
two
lines
to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and
selecting
help
opens
the
Help
file with the following text:

You can create your own data types in Visual Basic,
but
they
must
be
defined
first in a Type...End Type statement or in a properly
registered
object
library or type library. This error has the following
causes
and
solutions:
a.. You tried to declare a variable or

argument
with

an
undefined
data
type or you specified an unknown class or object name.
Use the Type statement in a module to define a new
data
type. If
you
are
trying to create a reference to a class, the class
must be
visible
to
the
project. If you are referring to a class in your
program,
you
must
have
a
class module of the specified name in your project.
Check
the
spelling
of
the type name or name of the object.

b.. The type you want to declare is in another
module
but
has
been
declared Private.
Move the definition of the type to a standard module
where
it
can be
Public.

 

[Klatuu]

Try sending the database to me. Do a Compact and Repair, and send just the
mdb. Don't zip. Send to (e-mail address removed)

I think this one has defeated us both, I typed "Call LoadFileNames()" into
the immediate window but nothing happens at all, I get no errors which I
suppose is a bonus but nothing populates the database. If I try "Sub
LoadFileNames()" or "Call Sub LoadFileNames()" it throws up errors, and not
being a coding person at all, its very difficult for me to look over the
code and see what might be wrong.

Unless you can see anything obvious I think its time to call it a day and
I'll see if I can find another method to achieve the end goal. But I
appreciate your input a great deal.

Wayne

The place to run it is in the Immediate window in the VB editor. It should
be a small window just below the editor window. If yours is not showing,
click View->Immediate Window on the main menu. Type it in there.
I would not mind taking a look at your database; however, I have tried that
before, and where I work always seems to block zip files and mdb files I have
tried to receive before.

If you think it might help, I could always email you the database as I
currently have it. You may be able to see the problem much easier if you're
looking at what I'm looking at?

Just a thought if you think it might help.

Regards,
Wayne

Please excuse my ignorance if I'm misunderstanding what you're saying - I
opened vb editor but where do I type the name of the sub "LoadFileNames"?

By opening the vb editor it brings me back to where I was before with the
original code and I already tried running that, eventhough its no longer
giving any errors, I still cant get it to populate the table in the
current
database.

Am I misunderstanding your instructions?



Not to worry, I want to see it through. To run it, open the vb editor.
You
can do that with alt F11. Then type in the same of the sub
LoadFileNames.
It should run and put the names in your table.
Please let me know if that helps.

:

You'll be pleased to know that has stopped any errors from reporting
so
it
does appear to have solved the problem, I've tried running the code by
selecting the Run menu and Run Sub/UserForm but alas nothing happens.
I've
checked the database table and I expected it to populate with entries
but so
far at least nothing has appeared.

I won't trouble you for any more assistance though as you have put far
too
much help into my little problem and i feel a little guilty for taking
up
all your time, if I could buy you a beer I would, I really am
grateful.

Thanks for a sterling effort nonetheless

Regards,
Wayne


Be glad when Monday is over. Another stupid left out the parens
problem:
If Not IsNull(DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'")) Then


:

The full code, as it now appears in my Access database reads as
follows:

Option Compare Database

Sub LoadFileNames()
Dim dbf As Database 'Object reference to the database
Dim rst As Recordset 'Object reference to the table
Dim txtFilePath As String 'Variable to hold the path to the
files
Dim txtFileName As String 'Variable to hold individual file
names

'Set up reference to your table
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("musiclist", dbOpenDynaset)

'Substitute your actual path in this line
txtFilePath = "G:\Music"
txtFileName = Dir(txtFilePath)
Do While txtFileName <> "" 'The Dir function will return
an
empty
string
'when all
matching
files
have been returned
'This line takes the extension off the file name
txtFileName = Left(txtFileName, Len(txtFileName) - 4)
'This line checks to see if the name is already in your table:
If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'") Then
With rst
.AddNew
![MUSIC_TITLE] = txtFileName
.Update
End With
End If
txtFileName = Dir
Loop
End Sub


Sorry if this is starting to annoy you, I'm really grateful for
your
help
though

Kind Regards,
Wayne



Wayne,
Could you please post back the code as it is now, I am getting
lost in
the
changes.
Thanks for you patience with my hastily written "air code"

:

I'm probably as confused as you, the right closed bracket does
make
sense
but its still throwing up a syntax error on that same code:

Compile error:

Syntax error

Is exactly what it says



If Not IsNull DLookup("[MUSIC_TITLE]", "musiclist",
_
"[MUSIC_TITLE] = '" & txtFileName & "'")
Then

Sorry, left out the right paren.

There

:

it doesn't seem to like the DLookup bit, its highlighting
that
part
specifically?

message
I think we may be getting somewhere, the errors from
earlier
are
no
longer
present and the only error now is a syntax error which I
assume
must
be
fairly simple, although I have looked at the code and
nothing
jumps
out at
me immediately.

It's highlighting the following as a syntax error:

If Not IsNull DLookup("[MUSIC_TITLE]",
"musiclist", _
"[MUSIC_TITLE] = '" & txtFileName & "'"
Then

Bear in mind also I have named the components as
follows:

Database: music
Table: musiclist
Field Name within Table: MUSIC_TITLE




message

Sorry If I did not make myself clear on the last post.
What
I
meant
was
to
change those two lines to:
Dim stringFilePath As String
Dim stringFileName As String


:

Hi again,
Similar problems this time too unfortunately. I
changed
the
two
lines
to
read:

Dim stringFilePath As Text
Dim stringFileName As Text

but it comes back with the same error:

Compile error:

User-defined type not defined

It gives the option to select OK or HELP, and
selecting
help
opens
the
Help
file with the following text: