IS THERE A WAY TO COPY A TABLE WITHOUT THE DATA? 2

[jflo]

I want to make a copy of a table and keep only the current record. I need to extract this information to fusion it in word. Am I better with copying the whole table and deleting what I don't need? Should I create a new table and all the fields and then copy the record I need? Is there a way of saving the current record of a recordset as a record in a new table?(that would be the best way to go if possible!) I uses Access 97.

 

[Mongooses]

Not sure exactly what you are trying to accomplish. If you have a recordset that contains the record you want and you don't need to store it then why not send the record directly to MS Word? But to answer your question, yes there is a simple way to create another table with the same structure.

Dim db As Database
Dim td As TableDef
Dim rs As Recordset
Dim cnt As Integer, i As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("[TableName to be Copied]&quot
Set td = db.CreateTableDef("[New Table Name]&quot

cnt = rs.Fields.Count

For i = 0 To cnt - 1
With td
.Fields.Append .Create(rs.Fields(i).Name, _
rs.Fields(i).Type, _
rs.Fields(i).Size)
End With
Next

db.TableDefs.Append td

That will create an identical table, all you need to do now is add the data. If you want to save each table with a unique name you can make the [New Table Name a variable(although depending on how many times you run this it could cause your database to have a lot of single record tables).

HTH,
JC

 

[jflo]

Thanks. I was doing something like what you suggested but in 40 steps instead of 10. You said I could send directly the record to word? Could you light up my lantern on this matter? I want to print a letter and an envelope automatically to the current client record. I taught of using a Word fusion documents to achieve this without the user being involved. What are your taughts on this?

 

[Mongooses]

After you have created a recordset with the appropriate record then you could:

Dim objWord As Object

Set objWord = CreateObject("Word.Application&quot
objWord.Visible = True
objWord.Documents.Add 'This will open a new word document

'ADD recordset DATA
Selection.TypeText text:= rs.Field("Name&quot
'ADD DOCUMENT TEXT

Set objWord = Nothing

End Sub

This will open MS Word and create a new document. There is probably a method to open an existing document but I haven't played with this much. The easiest way to add the correct code for your document is to open word record a macro and then view the code that the macro generated. You can then cut and paste the code into access.
Check microsoft's website for more accurate and complete help. Also you will need to go to Tools->References and select Microsoft Word Object Library.

HTH,
JC

 

[kandzii]

You can also use an existing Word Template and create a new document based on that template. I use the folowing code for doing that.

---------------------------------------------------------
Dim objword as object

On Error Resume Next
'Look if there is also a word application open.
'If not create a new word object
Set objword = GetObject(, "Word.application&quot

'Create a new word object if already open.
If objword Is Nothing Then
Set objword = CreateObject("Word.application&quot

'When there is still nothing returned give an
'error message that word is not installed.
If objword Is Nothing Then
'Give error message and leave procedure or function
MsgBox "Word is not installed on this computer!", vbExclamation, "Error opening word!"
'Exit procedure or function
Exit Sub
End If
End If

'Put screenupdating off for faster writing to word
'remember to put it back on when finished writing to word.
objword.screenupdating = false

'Create a new template based on an existing word template.
objword.documents.add "path" & "template.dot"

'Do the code for writing to your document.

'Put screenupdating back on and refresh the word document
objword.screenupdating = true
objword.screenrefresh

'Free up memory declared to the word object
Set objword = nothing
-----------------------------------------------------------

I hope this is of some help for you.

Regards,

Irwo Kandziora
Omni Trade Automatisering B.V.
Netherlands
irwo@omni-trade.nl

 

[jflo]

Thanks. I won't have much time to work on this until the first week on January. I'll let you know and post my findings. Happy holidays!