UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Tool To Extract Table Data Definition Statements From Access Databases, Any Version    
 
   
cheekybuddha
post Sep 28 2019, 08:27 AM
Post#1


UtterAccess Moderator
Posts: 13,059
Joined: 6-December 03
From: Telegraph Hill


Hi UA,

When posters are looking for help with their databases on UtterAccess they are often asked by those responding to post their table structure. Access gives no easy way to extract this info, and often newer users aren't really sure what's being asked of them anyway!

The solution is often to post their whole database, but not everyone responding is willing to download and run random db's from the internet (even from such esteemed sites such as UA!) or they don't have a new enough version to open the download.

In the world of SQLServer/MySQL/Oracle etc people are more used to sharing the details of their database via Data Defintion statements (DDL) which can be used to recreate the database tables and relationships.

Inspired by nvogel's post in this thread and other cries of frustration from MadPiet and others, I have hacked together a quick db which you can download and which you can choose and Access db, then select for which tables you want info (and optionally data), and it will output the DDL.

The DDL can then be pasted in to a post. Whomever is responding can then copy the DDL from a post and use the database to create the tables in a new or existing database on their machine.

I have only spent a few hours on the tool, so it's pretty basic at the moment. I'm sure it will barf when having to deal with dbs containing Attachment or Lookup fields!

I'm posting it here for testing and if folk find it useful, and when more features are added, I will post it to the Code Archive.

Todo list:
- Check whether a target db already contains the tables to be created - I haven't tested properly, but I'm sure an error will occur if this is the case! blush.gif
- Perhaps optionally add DROP statements to the DDL
- Add the ability to output resutlsets nicely formatted for pasting in to posts
- Prevent flashing when opening the database for extraction
- Limit the number of records extracted
- Allow creation of tables in an SQLServer or MySQL instance
- ...

Try it out and let me know what breaks/can be improved.

Cheers,

d thumbup.gif
Attached File(s)
Attached File  DDL_Extractor_v0.2.accdb.zip ( 137.13K )Number of downloads: 43
 

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Sep 29 2019, 10:51 PM
Post#2


UA Admin
Posts: 37,517
Joined: 20-June 02
From: Newcastle, WA


I haven’t had a lot of time to try it out. However one thing I’d like to see is a built—in way to save the SQL statements. Either in a table or as a text file.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
theDBguy
post Jun 2 2020, 11:37 PM
Post#3


UA Moderator
Posts: 78,499
Joined: 19-June 07
From: SunnySandyEggo


Sounds like fun. I'll give it a try tomorrow. I'm responding now, so I can bookmark this topic. Good night.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post Jun 3 2020, 08:52 AM
Post#4



Posts: 3,796
Joined: 27-February 09



David,
Oh COOL!!! I gotta check it out!!! After I play nice with it, should I try to break it and fix it?
THANKS!!!
Pieter

Ruh roh, looks like it's in 32-bit... and I have 64-bit installed... this could get interesting.
This post has been edited by MadPiet: Jun 3 2020, 08:58 AM
Go to the top of the page
 
FrankRuperto
post Jun 3 2020, 08:57 AM
Post#5



Posts: 1,113
Joined: 21-September 14
From: Tampa, Florida USA


Hi David,

Good idea. Will this tool work with any Access version, including mdb's and x64 accdb's?


This post has been edited by FrankRuperto: Jun 3 2020, 09:02 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
MadPiet
post Jun 3 2020, 09:01 AM
Post#6



Posts: 3,796
Joined: 27-February 09



Frank,

When I tried it, I get error messages about "Please review and update Declare statements and then mark them with the PtrSafe attribute"

Pieter
Go to the top of the page
 
FrankRuperto
post Jun 3 2020, 09:03 AM
Post#7



Posts: 1,113
Joined: 21-September 14
From: Tampa, Florida USA


Pieter,

Would be good for it to work on both 32 and 64-bit, since many new Access versions are x64.

Honestly speaking, I am sometimes skeptical of downloading Access db's and zipped folders from UA users I don't recognize.

Can UA check uploaded files for things like malicious vba code and other malware?
This post has been edited by FrankRuperto: Jun 3 2020, 09:04 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
cheekybuddha
post Jun 3 2020, 09:49 AM
Post#8


UtterAccess Moderator
Posts: 13,059
Joined: 6-December 03
From: Telegraph Hill


>> should I try to break it and fix it? <<

Definitely!

You won't find it hard to break it! I whipped it up quite quickly and haven't had a chance to look at it again since then.

Also, it's only ever been tested on 32bit (without any attempt to make it 64bit compatible).

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
MadPiet
post Jun 3 2020, 10:16 AM
Post#9



Posts: 3,796
Joined: 27-February 09



david,

so I guess I could read up on how to modify all the 32-bit calls so they work on 64-bit and then post back. =) I'll give it a bash... of course I might have to buy another license for Office, since the computer I seem to land it on invariably ends up in the shop. (Something tells me I might be a bit rough on laptops, maybe?)

Pieter
Go to the top of the page
 
cheekybuddha
post Jun 3 2020, 10:38 AM
Post#10


UtterAccess Moderator
Posts: 13,059
Joined: 6-December 03
From: Telegraph Hill


Hi Pieter,

I haven't looked at this in so long that I had forgotten that it used any API's!

There is a module basBrowse which uses old code to get a file dialog picker. This can be completely replaced with modern FileDialog code.

The other API's are for Clipboard stuff (from AccessWeb). I'd focus on those if you wish to convert. Someone has probably already done it, maybe they will share.

I'll update basBrowse now.

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 3 2020, 12:00 PM
Post#11


UtterAccess Moderator
Posts: 13,059
Joined: 6-December 03
From: Telegraph Hill


Hi,

You can substitute the code in basBrowse with this:
CODE
Option Compare Database
Option Explicit

Const msoFileDialogOpen         As Integer = 1, _
      msoFileDialogSaveAs       As Integer = 2, _
      msoFileDialogFilePicker   As Integer = 3, _
      msoFileDialogFolderPicker As Integer = 4

Function fGetFile( _
           iType As Integer, _
           Optional strDialogTitle As String, _
           Optional strInitialDir As String, _
           Optional strFileName As String, _
           Optional strDefaultExt As String, _
           Optional blMultiSelect As Boolean, _
           Optional vFilters As Variant _
         ) As String
  
  Dim i As Integer
  
  With Application.FileDialog(iType)
    .Title = strDialogTitle
    If Len(strInitialDir) Then .InitialFileName = strInitialDir
    Select Case iType
      Case msoFileDialogSaveAs
      ' https://stackoverflow.com/questions/7770030/preset-the-save-as-type-field-while-using-application-filedialogmsofiledialog
        For i = 1 To .Filters.Count
          With .Filters(i)
            If InStr(LCase(.Description), LCase(strDefaultExt)) > 0 _
            Or LCase(.Extensions) = LCase(strDefaultExt) Then
              .Parent.FilterIndex = i
              Exit For
            End If
          End With
        Next i
      Case Else
        .AllowMultiSelect = blMultiSelect
        With .Filters
          .Clear
          If IsArray(vFilters) Then
            For i = 0 To UBound(vFilters) Step 2
              .Add vFilters(i), vFilters(i + 1)
            Next i
          End If
          .Add "All files", "*.*"
        End With
    End Select
    If .Show Then fGetFile = .SelectedItems(1)
  End With

End Function

Function fGetAccessFile(Optional strTitle As String = "Browse for Access database ...") As String

  Dim vFilters As Variant
  
  vFilters = Array( _
               "Access (*.accdb)", "*.accdb", _
               "Access (*.accde)", "*.accde", _
               "Access (*.mdb)", "*.mdb", _
               "Access (*.mde)", "*.mde" _
             )

  fGetAccessFile = fGetFile( _
                     msoFileDialogFilePicker, _
                     strTitle, _
                     , _
                     , _
                     , _
                     False, _
                     vFilters _
                   )
  
End Function

Function fSaveAsAccessFile(Optional strTitle As String = "Choose Access database to export to ...") As String

  fSaveAsAccessFile = fGetFile( _
                        msoFileDialogSaveAs, _
                        strTitle, _
                        , _
                        , _
                        "Access" _
                      )

End Function


I seem to remember that I used the old API code so that you can specify the SaveAs file type which doesn't seem [easily] possible with the FileDialog object.

You'll have to make the changes to the Clipboard code as I don't have 64bit to test.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
FrankRuperto
post Jun 3 2020, 12:06 PM
Post#12



Posts: 1,113
Joined: 21-September 14
From: Tampa, Florida USA


Would Access 2010 x86 and x64 be good versions for using this tool on newer versions?

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
cheekybuddha
post Jun 3 2020, 12:12 PM
Post#13


UtterAccess Moderator
Posts: 13,059
Joined: 6-December 03
From: Telegraph Hill


>> Would Access 2010 x86 and x64 be good versions for using this tool on newer versions? <<

Your question doesn't quite make sense!

I think I used it on a more recent version of Access than 2010, but it was 32bit.

I wrote it using Access 2007, so it should work with newer versions. It might have a problem hooking in to db's created in newer versions of Access - that's the sort of feedback I'm hoping to find out.

--------------------


Regards,

David Marten
Go to the top of the page
 
MadPiet
post Jun 3 2020, 12:22 PM
Post#14



Posts: 3,796
Joined: 27-February 09



David,

I replaced the code as specified above. So that's okay now.

I get an error when I try to open the frmDDL form, and the compiler objects to the OnLoad event and OnResize event. (same PtrSafe error).

Does that help? If not, what information did I leave out?

thanks!
Pieter
Go to the top of the page
 
FrankRuperto
post Jun 3 2020, 12:24 PM
Post#15



Posts: 1,113
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE
>> Would Access 2010 x86 and x64 be good versions for using this tool on newer versions? <<

Your question doesn't quite make sense!

Maybe I'm misunderstanding how your tool works. Does it run in its own accdb and links to a target frontend, or does your tool's code need to be added to the target FE?
I was just trying to determine which is the best Access version for your tool that would make it upward compatible with any newer versions, if my first assumption is correct.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
cheekybuddha
post Jun 3 2020, 12:39 PM
Post#16


UtterAccess Moderator
Posts: 13,059
Joined: 6-December 03
From: Telegraph Hill


Pieter,

In form 'frmDDL' there is a button 'cmdCopy'.

Try commenting out the code in its Click event:
CODE
Private Sub cmdCopy_Click()

'  With Me
'    If Not CtlEmpty(.txtDDL) Then ClipBoard_SetText .txtDDL
'  End With
  
End Sub


Then comment out the whole of basClipboard.

Check it compiles, and try opening again. Obviously you lose the functionality of being able to copy the DDL with a button click, but you should be able to see what works.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 3 2020, 01:01 PM
Post#17


UtterAccess Moderator
Posts: 13,059
Joined: 6-December 03
From: Telegraph Hill


>> Maybe I'm misunderstanding how your tool works. <<

Yes, it runs standalone.

You choose the db from which you want to extract the DDL using the Browse button.

Click extract and the DDL is displayed on the form.

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 3 2020, 01:23 PM
Post#18


UtterAccess Moderator
Posts: 13,059
Joined: 6-December 03
From: Telegraph Hill


Here's a small update that appears to get rid of the flashing of the target database being opened.

(It sill includes the clipboard code, so anyone using 64bit please make the necessary adjustments)

thumbup.gif

d
Attached File(s)
Attached File  DDL_Extractor_v0.3.accdb.zip ( 114.2K )Number of downloads: 8
 

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Jun 3 2020, 05:27 PM
Post#19


UtterAccess VIP
Posts: 2,404
Joined: 4-June 18
From: Somerset, UK


Thanks David
I'm sure I responded with some suggestions when you first posted this tool last year.
However I can't see a reply here - was there originally another thread?

I think the tool is great & would like to see further development on it if you can spare the time to do so

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
cheekybuddha
post Jun 3 2020, 06:00 PM
Post#20


UtterAccess Moderator
Posts: 13,059
Joined: 6-December 03
From: Telegraph Hill


The other thread is linked in the first post, Collin.

>> if you can spare the time to do so <<

Ah, yes, there's the rub!

You think that in this time of lockdown one would have masses of spare time - if only it were so!

d

--------------------


Regards,

David Marten
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 11:17 AM