[Database] Type Mapping

[AlexMandel]

I've completed an initial mapping of the field types available in access and their corresponding matches in SQL server according to the built in Access XML Export tool.

It's available at http://dfgintranet/Portal/LinkClick.aspx?link=683&tabid=614

I've also collected some links relevant to creating/modifying databases via SQL code
http://www.islandman.org/umuc/ifsm410/DataDefinitionQuery.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp
http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx 

and documenting with XML
http://articles.techrepublic.com.com/5100-6329-5035228.html 

I have some code available for anyone interested in batch exporting their table schemas from an existing database, it could easily be modified to do the inverse too.


--

Alex Mandel
amandel@...
CA Dept. of Fish & Game
Resource Assessment Program (RAP)
916.445.4658
http://www.cdfg-rap.net 
http://www.dfg.ca.gov/rap/ 1812 9th St.
Sacramento, CA 95811

[Douglas Burch]

Alex,

I made a few additions/changes...take a look. I did not edit your xml schema except to change the ntext and nvarchar to basic sql server types, text and varchar, respectively. As we discussed before, the only thing the 'n' does is enable international characters...something I don't think we need (and, I believe there is overhead with it).

Also, thanks for the DDL info. I'm well familiar with DDL, however have not been able to execute these sql statements from VBA. Would you mind posting one of your routines as an example of how you implement it in a known, working VBA function? Thanks, Doug

>>> "Alex Mandel" <amandel@...> 1/3/2008 12:12 PM >>>

I've completed an initial mapping of the field types available in access and their corresponding matches in SQL server according to the built in Access XML Export tool.

It's available at http://dfgintranet/Portal/LinkClick.aspx?link=683&tabid=614

I've also collected some links relevant to creating/modifying databases via SQL code
http://www.islandman.org/umuc/ifsm410/DataDefinitionQuery.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp
http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx 

and documenting with XML
http://articles.techrepublic.com.com/5100-6329-5035228.html 

I have some code available for anyone interested in batch exporting their table schemas from an existing database, it could easily be modified to do the inverse too.

--

Alex Mandel
amandel@...
CA Dept. of Fish & Game
Resource Assessment Program (RAP)
916.445.4658
http://www.cdfg-rap.net 
http://www.dfg.ca.gov/rap/ 1812 9th St.
Sacramento, CA 95811


 

[Non-text portions of this message have been removed]

[AlexMandel]

What I showed were default types generated by the Access export tool, if we're going to modify them, then every time we run the export we'll have to run a search and replace on the keywords(not too hard to script). If the n type works well with the synch tools available from Microsoft, like the ADS then we might consider leaving them as is.

Here's an example of executing a sql statement in VBA in it's simplest form. This particular example doesn't have and indexes, keys or constraints but I'm pretty sure those work too.

Public Sub BuildDB()
 Dim strSQL As String
 strSQL = "CREATE TABLE TypeMap(TextBox TEXT(255),MemoBox MEMO,NumI INTEGER,NumD DOUBLE,Stamp DATETIME,Ok YESNO,AutoTest COUNTER);"
 CurrentDb.Execute strSQL
End Sub

-Alex

>>> On 1/7/2008 at 12:54, "Doug Burch" <dburch@...> wrote:
> Alex,
>
> I made a few additions/changes...take a look. I did not edit your xml schema
> except to change the ntext and nvarchar to basic sql server types, text and
> varchar, respectively. As we discussed before, the only thing the 'n' does is
> enable international characters...something I don't think we need (and, I
> believe there is overhead with it).
>
> Also, thanks for the DDL info. I'm well familiar with DDL, however have not
> been able to execute these sql statements from VBA. Would you mind posting
> one of your routines as an example of how you implement it in a known,
> working VBA function? Thanks, Doug
>
>>>> "Alex Mandel" <amandel@...> 1/3/2008 12:12 PM >>>
>
> I've completed an initial mapping of the field types available in access and
> their corresponding matches in SQL server according to the built in Access
> XML Export tool.
>
> It's available at http://dfgintranet/Portal/LinkClick.aspx?link=683&tabid=614 
>
> I've also collected some links relevant to creating/modifying databases via
> SQL code
> http://www.islandman.org/umuc/ifsm410/DataDefinitionQuery.html 
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html 
> /acintsql.asp
> http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx 
>
> and documenting with XML
> http://articles.techrepublic.com.com/5100-6329-5035228.html 
>
> I have some code available for anyone interested in batch exporting their
> table schemas from an existing database, it could easily be modified to do
> the inverse too.