How to create table from VBA code

[currentdb]

Hi all,

I was wondering how I can create a table from VBA code.

Actually my database contains a linked Excel sheet. I need to export the data from this linked Excel sheet into a table because I can't execute a merge into Word.

Your help is much appreciated.

Thanks

currentdb

 

[Magster]

Hi,

I don't have my reference books with me right now, but google on ADOX; it has what you need to create a table in Access. This is very similar to ADODB except it is geared towards creating tables, indexes, etc in VBA.

You'll also have to include the ADOX in your tools, references. Also, check this web site for ADOX too.

Sorry I can't give you more right now.

 

[currentdb]

Hi Magster,

I wasn't able to find the ADOX web site because there are many references on google.

Btw I created a update query, but even with that, I can't do a merge in Word.

Hope others can help.

thanks

currentdb

 

[Magster]

ADOX isn't a website, its part of Microsoft Access, but you have to include the library in the tools, references to use it. I believe it is
Microsoft ActiveX Data Objects library

I haven't done this for about 6 years, but if you go to the vba side of access and click help.

I just clicked the basic help, and type in ADOX you'll get a lot of info. When I learned how to use it, I used examples from books, but maybe the help docs have enough to help you - of course it depends upon how much vba you understand.

Using this isn't for the beginner without help, it isn't easy if you're working on your on (as I am) but my books became my associates!

There are some really good folks in this site that may offer some code examples which I think is the best way to learn, I just don't have any with me.

 

[marlan]

Hi,

This is a some code I have writen this week, you can have it:

Public Sub CreateMyTable()
    Dim Tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim fld2 As DAO.Field
    delLinkAndTable "MyTable" 'This is My Sub to delete a table, if exists
    Set Tdf = CurrentDb.CreateTableDef("MyTable")
    Set fld = Tdf.CreateField("Fld1", dbText)
    Set fld2 = Tdf.CreateField("Fld2", dbText)
    Tdf.Fields.Append fld
    Tdf.Fields.Append fld2
    CurrentDb.TableDefs.Append Tdf
End Sub

Press F1 for more option using old good DAO, it is simple and great!

 

[currentdb]

Hi marlan,

I inserted the code in a module and the table was created. Works like a charm! Thanks )

By the way, how I can adapt it so it can execute a merge into Word ? I think this one is more difficult. Instead of doing a manual merge in Word, it's better to execute this in a module and populate a specific template in Word. The Word template has the same fields as the ones that are in the table.

Thanks

currentdb

 

[marlan]

Sorry,
I hope other Forum members can help you on tha one!

 

[GComyn]

I've had this issue come up before, and I solved it by creating a report inside access that looked exactly like the word document, and ran the report from access... That way there was no problems if someone changed the word document by accident, or move it (which has happened).

GComyn

 

[currentdb]

Hi GComyn,

I did the same by creating a report into Access that looked like the one in Word. But the report was not exactly 100% the same like the one in Word and Access can't do all the formatting as Word can do. I ended by doing some cut/paste. The report was about 80% the same as the one in Word. The problem I had (and that I still have) is to figure a way how to update some fields. Now I'm trying to do this merge in Word from Access and I don't have any idea how complex it can be...