Klatuu said:The problem is you can't keep them in order. A relational database is not
like a spreadsheet.
Your problem can be solved, but you will have to think about the data as a
relational model rather than a flat speadsheet model. We humans are way
smarter than computers but not nearly as fast. It is easy for us to
interpret a blank row in a column means the row is related to the row above,
but a computer can't do that.
If you have a PN and it can be substituted with a Different PN from another
supplier, then you have to have a crossreference table to relate then.
If data is going to be coming in with a data element missing in a column,
importing it as a spreadsheet will not work. The way to accomplish what you
are wanting to do would require linking to the spreadsheet as a table, and
use recordset processing to read through the table. You wound need to
establish a variable to keep track of the current PN. When you hit a record
with an empty field, you would have to populate the field with the saved PN
before writing it to the Access table. Also, each time the PN changes in the
recordset, you would have to update the variable. Here is some sample pseudo
air code that might give you an idea:
Dim strPN As String
Dim rstExcel As DAO.Recordset
Dim rstAcc As DAO.Recordset
Dim dbf As DAO.Database
Set dbf = Currentdb
'Link To the spreadsheet
DoCmd.TransferSpreadsheet acLink, , Me.txtTableName, Me.txtImportPath,
intFieldName
Set rstExcel = dbf.OpenRecordset(Me.txtTableName)
Set rstAcc = dbf.OpenRecordset("MyAccessTable")
With rst.Excel
strPN = ![PN] 'Save the first PN
Do While Not .EOF
rstAcc.AddNew
If Nz(![PN], vbNullString) <> vbNullString Then
If ![PN] <> strPN Then
strPN = ![PN]
End If
End If
rstAcc![PN] = strPN
rstAcc![OtherFields] = ![OtherFields] ' One line for each field
rstAcc.Update
.MoveNext
Loop
End With
rstAcc.Close
rstExcel.Close
Set rstAcc = Nothing
Set rstExcel = Nothing
Set dbf = Nothing
Docmd.DeleteObject acTable, Me.txtTableName
--
Dave Hargis, Microsoft Access MVP
Del said:I understand what you are saying, but the spreadsheets I have to import are
created by my customer and they use them like a hard copy. One column is for
their part numbers which they put in one record with their primary mfr's PN
in the next column. If there is a substitute mfr's PN it is in the next
record but they do not put their PN in the same record with the sub PN. The
sub PN is only related to the customer's PN because it is listed below it and
before the next customer PN.
This is why it is essential to maintain the order of records when they are
imported.
Del said:I'm new to transfering spreadsheets. I was hoping I could import them and
fill in the PNs in Access. Working with your code will increase my
understanding.
Is it possible to use Access code to add a column of sequential numbers to
the spreadsheet that would make it possible to set them back in order after
the importation?
--
Thank you,
Del
Klatuu said:The problem is you can't keep them in order. A relational database is not
like a spreadsheet.
Your problem can be solved, but you will have to think about the data as a
relational model rather than a flat speadsheet model. We humans are way
smarter than computers but not nearly as fast. It is easy for us to
interpret a blank row in a column means the row is related to the row above,
but a computer can't do that.
If you have a PN and it can be substituted with a Different PN from another
supplier, then you have to have a crossreference table to relate then.
If data is going to be coming in with a data element missing in a column,
importing it as a spreadsheet will not work. The way to accomplish what you
are wanting to do would require linking to the spreadsheet as a table, and
use recordset processing to read through the table. You wound need to
establish a variable to keep track of the current PN. When you hit a record
with an empty field, you would have to populate the field with the saved PN
before writing it to the Access table. Also, each time the PN changes in the
recordset, you would have to update the variable. Here is some sample pseudo
air code that might give you an idea:
Dim strPN As String
Dim rstExcel As DAO.Recordset
Dim rstAcc As DAO.Recordset
Dim dbf As DAO.Database
Set dbf = Currentdb
'Link To the spreadsheet
DoCmd.TransferSpreadsheet acLink, , Me.txtTableName, Me.txtImportPath,
intFieldName
Set rstExcel = dbf.OpenRecordset(Me.txtTableName)
Set rstAcc = dbf.OpenRecordset("MyAccessTable")
With rst.Excel
strPN = ![PN] 'Save the first PN
Do While Not .EOF
rstAcc.AddNew
If Nz(![PN], vbNullString) <> vbNullString Then
If ![PN] <> strPN Then
strPN = ![PN]
End If
End If
rstAcc![PN] = strPN
rstAcc![OtherFields] = ![OtherFields] ' One line for each field
rstAcc.Update
.MoveNext
Loop
End With
rstAcc.Close
rstExcel.Close
Set rstAcc = Nothing
Set rstExcel = Nothing
Set dbf = Nothing
Docmd.DeleteObject acTable, Me.txtTableName
--
Dave Hargis, Microsoft Access MVP
Del said:I understand what you are saying, but the spreadsheets I have to import are
created by my customer and they use them like a hard copy. One column is for
their part numbers which they put in one record with their primary mfr's PN
in the next column. If there is a substitute mfr's PN it is in the next
record but they do not put their PN in the same record with the sub PN. The
sub PN is only related to the customer's PN because it is listed below it and
before the next customer PN.
This is why it is essential to maintain the order of records when they are
imported.
--
Thank you,
Del
:
#1 Why do you say its essential that their kept in order, grasshopper?
#2 The nature of a relational database is such that you should never count
on records appearing in any particular order. If what you're trying to
accomplish requires your records to be stored in a specific order in the
database, I would suggest that path that you're taking to your goal is the
long way...which leads me to #3...
#3 The order in which records appear in a database is irrelevant given the
numerous means available to work with the records
:
I'm using TransferSpreadsheet method to import excel into Access 2000, but
the records are scrambled after import. It is essential to keep them in
order.
Here is my code:
DoCmd.TransferSpreadsheet , , Me.txtTableName, Me.txtImportPath, intFieldName
Klatuu said:Actually, you could do it with very code at all.
Do not let the Transfer create the table. Create the table yourself and add
an Autonumber field. Name the other fields the same as in the header row of
the spreadsheet. When you import the spreadsheet, a sequential number will be
added for each row imported.
But still, to work with the data, you need a query sorted on the autonumber
field.
Note that when you import using an exiting table, the imort does not
overwrite the existing data, it appends new records to the old. You will
have to a a delete query to your routine to delete the data from the table
before the import if you only want new data in the table.
--
Dave Hargis, Microsoft Access MVP
Del said:I'm new to transfering spreadsheets. I was hoping I could import them and
fill in the PNs in Access. Working with your code will increase my
understanding.
Is it possible to use Access code to add a column of sequential numbers to
the spreadsheet that would make it possible to set them back in order after
the importation?
--
Thank you,
Del
Klatuu said:The problem is you can't keep them in order. A relational database is not
like a spreadsheet.
Your problem can be solved, but you will have to think about the data as a
relational model rather than a flat speadsheet model. We humans are way
smarter than computers but not nearly as fast. It is easy for us to
interpret a blank row in a column means the row is related to the row above,
but a computer can't do that.
If you have a PN and it can be substituted with a Different PN from another
supplier, then you have to have a crossreference table to relate then.
If data is going to be coming in with a data element missing in a column,
importing it as a spreadsheet will not work. The way to accomplish what you
are wanting to do would require linking to the spreadsheet as a table, and
use recordset processing to read through the table. You wound need to
establish a variable to keep track of the current PN. When you hit a record
with an empty field, you would have to populate the field with the saved PN
before writing it to the Access table. Also, each time the PN changes in the
recordset, you would have to update the variable. Here is some sample pseudo
air code that might give you an idea:
Dim strPN As String
Dim rstExcel As DAO.Recordset
Dim rstAcc As DAO.Recordset
Dim dbf As DAO.Database
Set dbf = Currentdb
'Link To the spreadsheet
DoCmd.TransferSpreadsheet acLink, , Me.txtTableName, Me.txtImportPath,
intFieldName
Set rstExcel = dbf.OpenRecordset(Me.txtTableName)
Set rstAcc = dbf.OpenRecordset("MyAccessTable")
With rst.Excel
strPN = ![PN] 'Save the first PN
Do While Not .EOF
rstAcc.AddNew
If Nz(![PN], vbNullString) <> vbNullString Then
If ![PN] <> strPN Then
strPN = ![PN]
End If
End If
rstAcc![PN] = strPN
rstAcc![OtherFields] = ![OtherFields] ' One line for each field
rstAcc.Update
.MoveNext
Loop
End With
rstAcc.Close
rstExcel.Close
Set rstAcc = Nothing
Set rstExcel = Nothing
Set dbf = Nothing
Docmd.DeleteObject acTable, Me.txtTableName
--
Dave Hargis, Microsoft Access MVP
:
I understand what you are saying, but the spreadsheets I have to import are
created by my customer and they use them like a hard copy. One column is for
their part numbers which they put in one record with their primary mfr's PN
in the next column. If there is a substitute mfr's PN it is in the next
record but they do not put their PN in the same record with the sub PN. The
sub PN is only related to the customer's PN because it is listed below it and
before the next customer PN.
This is why it is essential to maintain the order of records when they are
imported.
--
Thank you,
Del
:
#1 Why do you say its essential that their kept in order, grasshopper?
#2 The nature of a relational database is such that you should never count
on records appearing in any particular order. If what you're trying to
accomplish requires your records to be stored in a specific order in the
database, I would suggest that path that you're taking to your goal is the
long way...which leads me to #3...
#3 The order in which records appear in a database is irrelevant given the
numerous means available to work with the records
:
I'm using TransferSpreadsheet method to import excel into Access 2000, but
the records are scrambled after import. It is essential to keep them in
order.
Here is my code:
DoCmd.TransferSpreadsheet , , Me.txtTableName, Me.txtImportPath, intFieldName
Del said:Great. That gives me an alternative.
I've been experimenting with linking to the spreadsheet and I've run into a
problem with the data type. Most of the spreadsheet columns contain number &
text but the linked table has some of them as numbers only. The records that
should have text simply display #Num!. Is there some way to avoid this?
--
Thank you,
Del
Klatuu said:Actually, you could do it with very code at all.
Do not let the Transfer create the table. Create the table yourself and add
an Autonumber field. Name the other fields the same as in the header row of
the spreadsheet. When you import the spreadsheet, a sequential number will be
added for each row imported.
But still, to work with the data, you need a query sorted on the autonumber
field.
Note that when you import using an exiting table, the imort does not
overwrite the existing data, it appends new records to the old. You will
have to a a delete query to your routine to delete the data from the table
before the import if you only want new data in the table.
--
Dave Hargis, Microsoft Access MVP
Del said:I'm new to transfering spreadsheets. I was hoping I could import them and
fill in the PNs in Access. Working with your code will increase my
understanding.
Is it possible to use Access code to add a column of sequential numbers to
the spreadsheet that would make it possible to set them back in order after
the importation?
--
Thank you,
Del
:
The problem is you can't keep them in order. A relational database is not
like a spreadsheet.
Your problem can be solved, but you will have to think about the data as a
relational model rather than a flat speadsheet model. We humans are way
smarter than computers but not nearly as fast. It is easy for us to
interpret a blank row in a column means the row is related to the row above,
but a computer can't do that.
If you have a PN and it can be substituted with a Different PN from another
supplier, then you have to have a crossreference table to relate then.
If data is going to be coming in with a data element missing in a column,
importing it as a spreadsheet will not work. The way to accomplish what you
are wanting to do would require linking to the spreadsheet as a table, and
use recordset processing to read through the table. You wound need to
establish a variable to keep track of the current PN. When you hit a record
with an empty field, you would have to populate the field with the saved PN
before writing it to the Access table. Also, each time the PN changes in the
recordset, you would have to update the variable. Here is some sample pseudo
air code that might give you an idea:
Dim strPN As String
Dim rstExcel As DAO.Recordset
Dim rstAcc As DAO.Recordset
Dim dbf As DAO.Database
Set dbf = Currentdb
'Link To the spreadsheet
DoCmd.TransferSpreadsheet acLink, , Me.txtTableName, Me.txtImportPath,
intFieldName
Set rstExcel = dbf.OpenRecordset(Me.txtTableName)
Set rstAcc = dbf.OpenRecordset("MyAccessTable")
With rst.Excel
strPN = ![PN] 'Save the first PN
Do While Not .EOF
rstAcc.AddNew
If Nz(![PN], vbNullString) <> vbNullString Then
If ![PN] <> strPN Then
strPN = ![PN]
End If
End If
rstAcc![PN] = strPN
rstAcc![OtherFields] = ![OtherFields] ' One line for each field
rstAcc.Update
.MoveNext
Loop
End With
rstAcc.Close
rstExcel.Close
Set rstAcc = Nothing
Set rstExcel = Nothing
Set dbf = Nothing
Docmd.DeleteObject acTable, Me.txtTableName
--
Dave Hargis, Microsoft Access MVP
:
I understand what you are saying, but the spreadsheets I have to import are
created by my customer and they use them like a hard copy. One column is for
their part numbers which they put in one record with their primary mfr's PN
in the next column. If there is a substitute mfr's PN it is in the next
record but they do not put their PN in the same record with the sub PN. The
sub PN is only related to the customer's PN because it is listed below it and
before the next customer PN.
This is why it is essential to maintain the order of records when they are
imported.
--
Thank you,
Del
:
#1 Why do you say its essential that their kept in order, grasshopper?
#2 The nature of a relational database is such that you should never count
on records appearing in any particular order. If what you're trying to
accomplish requires your records to be stored in a specific order in the
database, I would suggest that path that you're taking to your goal is the
long way...which leads me to #3...
#3 The order in which records appear in a database is irrelevant given the
numerous means available to work with the records
:
I'm using TransferSpreadsheet method to import excel into Access 2000, but
the records are scrambled after import. It is essential to keep them in
order.
Here is my code:
DoCmd.TransferSpreadsheet , , Me.txtTableName, Me.txtImportPath, intFieldName
Klatuu said:No, I don't have a silver bullet for you. The problem is Access looks at the
first several rows of an Excel file to guess at the data type. I don't
remember exactly how many and I also know there is a registry setting you can
change to make it look at more, but I really avoid messing with the registry,
so I have never done it.
You may try formatting the spreadsheet.
--
Dave Hargis, Microsoft Access MVP
Del said:Great. That gives me an alternative.
I've been experimenting with linking to the spreadsheet and I've run into a
problem with the data type. Most of the spreadsheet columns contain number &
text but the linked table has some of them as numbers only. The records that
should have text simply display #Num!. Is there some way to avoid this?
--
Thank you,
Del
Klatuu said:Actually, you could do it with very code at all.
Do not let the Transfer create the table. Create the table yourself and add
an Autonumber field. Name the other fields the same as in the header row of
the spreadsheet. When you import the spreadsheet, a sequential number will be
added for each row imported.
But still, to work with the data, you need a query sorted on the autonumber
field.
Note that when you import using an exiting table, the imort does not
overwrite the existing data, it appends new records to the old. You will
have to a a delete query to your routine to delete the data from the table
before the import if you only want new data in the table.
--
Dave Hargis, Microsoft Access MVP
:
I'm new to transfering spreadsheets. I was hoping I could import them and
fill in the PNs in Access. Working with your code will increase my
understanding.
Is it possible to use Access code to add a column of sequential numbers to
the spreadsheet that would make it possible to set them back in order after
the importation?
--
Thank you,
Del
:
The problem is you can't keep them in order. A relational database is not
like a spreadsheet.
Your problem can be solved, but you will have to think about the data as a
relational model rather than a flat speadsheet model. We humans are way
smarter than computers but not nearly as fast. It is easy for us to
interpret a blank row in a column means the row is related to the row above,
but a computer can't do that.
If you have a PN and it can be substituted with a Different PN from another
supplier, then you have to have a crossreference table to relate then.
If data is going to be coming in with a data element missing in a column,
importing it as a spreadsheet will not work. The way to accomplish what you
are wanting to do would require linking to the spreadsheet as a table, and
use recordset processing to read through the table. You wound need to
establish a variable to keep track of the current PN. When you hit a record
with an empty field, you would have to populate the field with the saved PN
before writing it to the Access table. Also, each time the PN changes in the
recordset, you would have to update the variable. Here is some sample pseudo
air code that might give you an idea:
Dim strPN As String
Dim rstExcel As DAO.Recordset
Dim rstAcc As DAO.Recordset
Dim dbf As DAO.Database
Set dbf = Currentdb
'Link To the spreadsheet
DoCmd.TransferSpreadsheet acLink, , Me.txtTableName, Me.txtImportPath,
intFieldName
Set rstExcel = dbf.OpenRecordset(Me.txtTableName)
Set rstAcc = dbf.OpenRecordset("MyAccessTable")
With rst.Excel
strPN = ![PN] 'Save the first PN
Do While Not .EOF
rstAcc.AddNew
If Nz(![PN], vbNullString) <> vbNullString Then
If ![PN] <> strPN Then
strPN = ![PN]
End If
End If
rstAcc![PN] = strPN
rstAcc![OtherFields] = ![OtherFields] ' One line for each field
rstAcc.Update
.MoveNext
Loop
End With
rstAcc.Close
rstExcel.Close
Set rstAcc = Nothing
Set rstExcel = Nothing
Set dbf = Nothing
Docmd.DeleteObject acTable, Me.txtTableName
--
Dave Hargis, Microsoft Access MVP
:
I understand what you are saying, but the spreadsheets I have to import are
created by my customer and they use them like a hard copy. One column is for
their part numbers which they put in one record with their primary mfr's PN
in the next column. If there is a substitute mfr's PN it is in the next
record but they do not put their PN in the same record with the sub PN. The
sub PN is only related to the customer's PN because it is listed below it and
before the next customer PN.
This is why it is essential to maintain the order of records when they are
imported.
--
Thank you,
Del
:
#1 Why do you say its essential that their kept in order, grasshopper?
#2 The nature of a relational database is such that you should never count
on records appearing in any particular order. If what you're trying to
accomplish requires your records to be stored in a specific order in the
database, I would suggest that path that you're taking to your goal is the
long way...which leads me to #3...
#3 The order in which records appear in a database is irrelevant given the
numerous means available to work with the records
:
I'm using TransferSpreadsheet method to import excel into Access 2000, but
the records are scrambled after import. It is essential to keep them in
order.
Here is my code:
DoCmd.TransferSpreadsheet , , Me.txtTableName, Me.txtImportPath, intFieldName
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.